Oracle表領域使用率SQLスクリプトの表示
10420 ワード
記事:http://blog.csdn.net/tianlesoftware/article/details/7619732
効果図筆者が追加:
1 /* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) */
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL --
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1;
効果図筆者が追加: