表空间使用率查询sql

表空间sql

select tpsname "表空间名",bytesize as "表空间大小(M)", maxsize "最大可扩空间(M)",max_used as "使用率%" from (SELECT  d.tablespace_name  tpsname,d.status status,d.segment_space_management mgr, d.contents type,        TO_CHAR(NVL(trunc(A.maxbytes / 1024 / 1024), 0),'99G999G990') maxsize,TO_CHAR(NVL(trunc(A.bytes / 1024 / 1024), 0),'99G999G990') bytesize,TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990D00') c_userd,TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0),'990D00') max_used  FROM sys.dba_tablespaces d,(SELECT tablespace_name,sum(bytes) bytes,SUM(case autoextensible when  'NO'  then BYTES when  'YES' then MAXBYTES else null end ) maxbytes   FROM dba_data_files  GROUP BY tablespace_name) a,(SELECT tablespace_name,SUM(bytes) bytes, MAX(bytes) largest_free   FROM dba_free_space   GROUP BY tablespace_name) f  WHERE d.tablespace_name = a.tablespace_name   AND d.tablespace_name = f.tablespace_name(+)) where  max_used>=55 order by max_used desc;