表空间使用率

来源：互联网发布：淘宝官方买家秀入口编辑：程序博客网时间：2024/05/18 00:20

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

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.>个人最常用

COL SIZE_G FOR A15COL FREE_G FOR A15COL USED_PCT FOR A10COL TABLESPACE_NAME FOR A30SELECT 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_pctFROM   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) fWHERE  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.>个人第二常用

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.>

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.>

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.>

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'

0 0