Oracle表空间使用情况查询和管理

--查询表空间使用情况:

 1 SELECT D.TABLESPACE_NAME,
 2        SPACE || 'M' "SUM_SPACE(M)",
 3        BLOCKS "SUM_BLOCKS",
 4        SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
 5        ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
 6        FREE_SPACE || 'M' "FREE_SPACE(M)"
 7   FROM (SELECT TABLESPACE_NAME,
 8                ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
 9                SUM(BLOCKS) BLOCKS
10           FROM DBA_DATA_FILES
11          GROUP BY TABLESPACE_NAME) D,
12        (SELECT TABLESPACE_NAME,
13                ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
14           FROM DBA_FREE_SPACE
15          GROUP BY TABLESPACE_NAME) F
16  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
17 UNION ALL
18 SELECT D.TABLESPACE_NAME,
19        SPACE || 'M' "SUM_SPACE(M)",
20        BLOCKS SUM_BLOCKS,
21        USED_SPACE || 'M' "USED_SPACE(M)",
22        ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
23        NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
24   FROM (SELECT TABLESPACE_NAME,
25                ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
26                SUM(BLOCKS) BLOCKS
27           FROM DBA_TEMP_FILES
28          GROUP BY TABLESPACE_NAME) D,
29        (SELECT TABLESPACE_NAME,
30                ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
31                ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
32           FROM V$TEMP_SPACE_HEADER
33          GROUP BY TABLESPACE_NAME) F
34  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
35  ORDER BY 5 desc;
View Code

 --查看数据文件的使用情况:

1 select a.file_name,
2        a.bytes / 1024 / 1024 "TOTAL(M)",
3        b.sb / 1024 / 1024 "FREE(M)",
4        100 * b.sb / a.bytes "FREE%"
5   from dba_data_files a,
6        (select file_id, sum(BYTES) sb from dba_free_space group by file_id) b
7  where a.file_id = b.file_id(+)
8    and a.tablespace_name = 'NNC_INDEX02'
9  order by a.file_name;
View Code

 

posted @ 2017-12-13 14:14  文水凡  阅读(226)  评论(0)    收藏  举报