12C 包含PDB 的
SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME, D.TABLESPACE_NAME, TOTAL_SPACE, (SPACE - NVL(FREE_SPACE, 0)), ROUND((SPACE - NVL(FREE_SPACE, 0)) / TOTAL_SPACE * 100, 2) ratio, FREE_SPACE FROM (SELECT TABLESPACE_NAME, con_id, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) / (1024 * 1024), 2) TOTAL_SPACE, SUM(BLOCKS) BLOCKS FROM containers(DBA_DATA_FILES) GROUP BY TABLESPACE_NAME, con_id) D, (SELECT TABLESPACE_NAME, con_id, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM containers(DBA_FREE_SPACE) GROUP BY TABLESPACE_NAME, con_id) F, v$pdbs t WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.CON_ID = F.CON_ID and F.con_id = t.CON_ID(+) UNION ALL SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME, D.TABLESPACE_NAME, SPACE, USED_SPACE, ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) ratio, NVL(FREE_SPACE, 0) FROM (SELECT TABLESPACE_NAME, con_id, ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS FROM containers(DBA_TEMP_FILES) GROUP BY TABLESPACE_NAME, con_id) D, (SELECT TABLESPACE_NAME, con_id, ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM containers(V$TEMP_SPACE_HEADER) GROUP BY TABLESPACE_NAME, con_id) F, v$pdbs t WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.CON_ID = F.CON_ID and F.con_id = t.CON_ID(+) ORDER BY 1
12C
SELECT F.TABLESPACE_NAME, (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)", F.FREE_SPACE / 1024 "FREE (GB)", T.TOTAL_SPACE / 1024 "TOTAL(GB)", (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FREE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT VALUE / 1024 FROM V$PARAMETER WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE FROM CDB_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM CDB_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
11G
SELECT DF.TABLESPACE_NAME, COUNT(*) DATAFILE_COUNT, ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB, ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB, ROUND(SUM(DF.BYTES) / 1048576 / 1024 - SUM(FREE.BYTES) / 1048576 / 1024, 2) USED_GB, ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE, 100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED, ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE FROM DBA_DATA_FILES DF, (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) BYTES, MAX(BYTES) MAXBYTES FROM DBA_FREE_SPACE WHERE BYTES > 1024 * 1024 GROUP BY TABLESPACE_NAME, FILE_ID) FREE WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+) AND DF.FILE_ID = FREE.FILE_ID(+) GROUP BY DF.TABLESPACE_NAME ORDER BY 8;
运行示例
SYS@edbdb1> SELECT DF.TABLESPACE_NAME, COUNT(*) DATAFILE_COUNT, ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB, ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB, ROUND(SUM(DF.BYTES) / 1048576 / 1024 - SUM(FREE.BYTES) / 1048576 / 1024, 2) USED_GB, ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE, 100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED, ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE FROM DBA_DATA_FILES DF, (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) BYTES, MAX(BYTES) MAXBYTES FROM DBA_FREE_SPACE WHERE BYTES > 1024 * 1024 GROUP BY TABLESPACE_NAME, FILE_ID) FREE WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+) AND DF.FILE_ID = FREE.FILE_ID(+) GROUP BY DF.TABLESPACE_NAME ORDER BY 8; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 TABLESPACE_NAME DATAFILE_COUNT SIZE_GB FREE_GB USED_GB MAXFREE PCT_USED PCT_FREE ------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM 1 3.95 .01 3.94 .01 99.83 .17 ODS_EDCDATA_IDX 379 11838.87 476.56 11362.31 3.88 95.97 4.03 ODS_EDCDATA_DAT 428 13507.56 610.1 12897.46 3.88 95.48 4.52 ODS_LOTHIST_IDX 62 1908.59 134.11 1774.47 3.88 92.97 7.03 TRF_RUNHIST_IDX 30 945.97 81.17 864.8 3.09 91.42 8.58 TRF_RUNHIST_DAT 46 1471.96 127.77 1344.19 3.88 91.32 8.68 ODS_CUSDATA_IDX 28 895.97 103.93 792.04 3.88 88.4 11.6 SUM_PRODSUM_IDX 9 287.99 35.78 252.21 3.88 87.58 12.42 TRF_EDCTRNS_IDX 5 154 19.34 134.66 3.88 87.44 12.56 TRF_CUSTRNS_IDX 20 639.98 93.44 546.54 3.88 85.4 14.6 TRF_CUSTRNS_DAT 11 339.99 118.66 221.33 3.88 65.1 34.9 ODS_LOTHIST_DAT 234 7077.48 3326.07 3751.41 3.88 53 47 ODS_CUSDATA_DAT 47 1503.95 776.27 727.68 3.88 48.38 51.62 SUM_PRODSUM_DAT 11 339.99 177.46 162.54 3.88 47.81 52.19 SYSAUX 2 31.34 22.09 9.25 1.27 29.53 70.47 UNDOTBS2 4 128 93.25 34.75 3.88 27.15 72.85 UNDOTBS1 4 127.96 97.51 30.45 3.88 23.8 76.2 TRF_EDCTRNS_DAT 11 339.99 261.1 78.89 3.88 23.2 76.8 USERS 1 .01 .01 0 .01 16.5 83.5 ETL_JOBDATA_IDX 1 32 29.36 2.64 3.88 8.26 91.74 ETL_JOBDATA_DAT 3 96 94.5 1.49 3.88 1.56 98.44 WEB_JOBDATA_IDX 1 32 31.83 .17 3.88 .52 99.48 WEB_JOBDATA_DAT 3 96 95.8 .2 3.88 .21 99.79 23 rows selected. SYS@edbdb1>
