Oracle表空间使用率查询SQL

附加临时段
SET LINES 132 PAGES 300
col EXT_PCT for a10
COL con_name FORM A15 HEAD “Container|Name”
COL tablespace_name FORM A25
COL fsm FORM 999,999,999,999 HEAD “Free|Space Meg.”
COL apm FORM 999,999,999,999 HEAD “Alloc|Space Meg.”

COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name

WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
FROM cdb_free_space cf1
,v$containersc1WHEREcf1.co{n}_{i}d=c1.co{n}_{i}dGROUPBYc1.co{n}_{i}d,cf1.tablespac{e}_{n}ame),yAS(SELECTc2.co{n}_{i}d,cd.tablespac{e}_{n}ame,sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024apmFROMcd{b}_{d}at{a}_{f}ilescd,v$containers c2
WHERE cd.con_id = c2.con_id
GROUP BY c2.con_id
,cd.tablespace_name)
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm , trunc(x.fsm/y.apm*100,2)||‘%’ ext_pct
FROM x, y, v$containers v
WHERE x.con_id = y.con_id
AND x.tablespace_name = y.tablespace_name
AND v.con_id = y.con_id
ORDER BY 1,6,2,3;

不附加临时段
set linesize 300;
SELECT B.TABLESPACE_NAME,
ROUND(sum(case when B.MAXBYTES=0 then B.bytes else B.maxbytes end)/1024/1024/1024,0)SUM_GB,
ROUND (SUM(B.bytes/1024/1024/1024)-SUM(A.bytes/1024/1024/1024), 0) used_GB,
ROUND(sum(case when B.MAXBYTES=0 then B.bytes else B.maxbytes end)/1024/1024/1024-(SUM(B.bytes/1024/1024/1024)-SUM(A.bytes/1024/1024/1024)),0) free_GB,
ROUND((SUM(B.bytes/1024/1024/1024)-SUM(A.bytes/1024/1024/1024))/(sum(case when B.MAXBYTES=0 then B.bytes else B.maxbytes end)/1024/1024/1024), 4) * 100 USE_PRECENT
FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) A,
DBA_DATA_FILES B
WHERE A.FILE_ID(+) = B.FILE_ID
AND A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
GROUP BY B.TABLESPACE_NAME
ORDER BY USE_PRECENT desc;