表空间使用率

84 阅读 0 评论 56 点赞

我是靠谱客的博主沉静小蚂蚁，这篇文章主要介绍表空间使用率，现在分享给大家，希望可以做个参考。

统计月使用量，按表空间排序

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT d.tablespace_name,  
       to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,  
       to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,  
       to_char(nvl((a.bytes-f.bytes) / 1024 / 1024 / 1024, 0), '99,999,990.00')  used_g,
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct  
FROM   dba_tablespaces d,  
       (SELECT tablespace_name, SUM(bytes) bytes  
        FROM   dba_data_files  
        GROUP  BY tablespace_name) a,  
       (SELECT tablespace_name, SUM(bytes) bytes  
        FROM   dba_free_space  
        GROUP  BY tablespace_name) f  
WHERE  d.tablespace_name = a.tablespace_name(+)  
       AND d.tablespace_name = f.tablespace_name(+)  
       AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')  
ORDER  BY 1;

1.>个人最常用

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
       to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
       to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
FROM   dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM   dba_data_files
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM   dba_free_space
        GROUP  BY tablespace_name) f
WHERE  d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
ORDER  BY 4 DESC;

2.>个人第二常用

复制代码

1
2
3
4
5
6
7
SELECT A.TABLESPACE_NAME,
          A.BYTES TOTAL,
       B.BYTES USED,
       (B.BYTES) / A.BYTES "USED %"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

3.>

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
SELECT A.TABLESPACE_NAME "表空间名",
       A.TOTAL_SPACE "总空间(G)",
       NVL(B.FREE_SPACE, 0) "剩余空间(G)",
       A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
       CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0
  FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024  ),2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

4.>

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT A.TABLESPACE_NAME "表空间名",
       A.TOTAL_SPACE "总空间(G)",
       NVL(B.FREE_SPACE, 0) "剩余空间(G)",
       A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
       CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0
  FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024  ),2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
 ORDER BY 5;

5.>

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT D.TABLESPACE_NAME "表空间名称",
       SPACE || 'G' "总空间大小(G)",
       BLOCKS "总块数",
       SPACE - NVL (FREE_SPACE, 0) || 'G' "已用空间(G)",
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
          "使用百分比(%)",
       FREE_SPACE || 'G' "剩余空间(G)"
  FROM (  SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES) / (1024 * 1024 * 1024), 2) SPACE,
                 SUM (BLOCKS) BLOCKS
            FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) D,
       (  SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES) / (1024 * 1024 * 1024), 2) FREE_SPACE
            FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.TABLESPACE_NAME='TBS_BW'