Oracle sql tablespace utilization
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
