Oracle12c, 18c, 19c tablespace usage query
Query temporary tablespace
SELECT tt.con_id
, nvl (x.name, 'CDB$ROOT') AS DB_NAME
, ts1.tablespace_name AS "RES_NAME"
, round (nvl (tt.tmp_max_size, 0) / 1024 / 1024, 2) AS "TABLE_SIZE"
, round (nvl (tu.tmp_used_size, 0) / 1024 / 1024, 2) AS "USED_SIZE"
, CASE
WHEN tt.tmp_space = 0
THEN 0
ELSE ROUND ((nvl (tu.tmp_used_size, 0) * 100 / tt.tmp_max_size), 2)
END AS "USE_PERCENT"
, round ((nvl (tt.tmp_max_size, 0)-nvl (tu.tmp_used_size, 0) / 1024 / 1024, 2) AS "AVA_SIZE"
, ts1.CONTENTS AS "CONTENTS"
, ts1.STATUS AS "STATUS"
, ts1.ALLOCATION_TYPE AS "ALLOCATION_TYPE"
, tt.tmp_file_count AS "FILE_COUNT"
, CASE
WHEN tt.tmp_auto_extens_c > 0
THEN 'YES'
ELSE 'NO'
END AS "AUTOEXTENSIBLE"
FROM cdb_tablespaces ts1
, v$pdbs x
, (
SELECT tablespace_name
, sum (nvl (bytes, 0)) / 1024 tmp_space
, con_id
, SUM (decode (AUTOEXTENSIBLE, 'YES', nvl (MAXBYTES, 0), nvl (bytes, 0)) / 1024 / 1024 tmp_max_size
, count (*) tmp_file_count
, sum (decode (AUTOEXTENSIBLE, 'YES', 1,0)) tmp_auto_extens_c
FROM cdb_temp_files
GROUP BY tablespace_name
, con_id
) tt
, (
SELECT tablespace_name
, SUM (nvl (bytes_cached, 0)) / 1024 / 1024 tmp_used_size
FROM gv$temp_extent_pool
GROUP BY tablespace_name) tu
WHERE tt.tablespace_name = tu.tablespace_name
AND ts1.extent_management LIKE 'LOCAL'
AND ts1.contents LIKE 'TEMPORARY'
AND tt.tablespace_name = ts1.TABLESPACE_NAME
AND tt.con_id = ts1.CON_ID
AND ts1.con_id = x.con_id (+)
Query undo and data tablespaces
SELECT d.con_id
, nvl (x.name, 'CDB$ROOT') AS DB_NAME
, d.tablespace_name AS "RES_NAME"
, round (d.max_size / 1024 / 1024, 2) AS "TABLE_SIZE"
, round (d.SPACE-NVL (f.FREE_SPACE, 0)) / 1024 / 1024, 2) AS "USED_SIZE"
, CASE
WHEN d.space = 0
THEN 0
ELSE ROUND (d.SPACE-NVL (f.FREE_SPACE, 0)) * 100 / d.max_size), 2)
END AS "USE_PERCENT"
, round ((d.max_size-d.space + NVL (f.FREE_SPACE, 0)) / 1024 / 1024, 2) AS "AVA_SIZE"
, ts.CONTENTS AS "CONTENTS"
, CASE
WHEN ts.STATUS = 'READ ONLY'
AND d.offline_c = d.file_count
THEN 'OFFLINE (READ_ONLY)'
ELSE ts.STATUS
END AS "STATUS"
, ts.ALLOCATION_TYPE AS "ALLOCATION_TYPE"
, d.file_count AS "FILE_COUNT"
, CASE
WHEN d.auto_extens_c > 0
THEN 'YES'
ELSE 'NO'
END AS "AUTOEXTENSIBLE"
FROM cdb_tablespaces ts
, v$pdbs x
, (
SELECT TABLESPACE_NAME
, con_id
, SUM (nvl (BYTES, 0)) / 1024 SPACE
, sum (decode (autoextensible, 'YES', nvl (maxbytes, 0), nvl (bytes, 0)) / 1024 max_size
, sum (decode (ONLINE_STATUS, 'OFFLINE', 1,0)) offline_c
, count (*) file_count
, sum (decode (autoextensible, 'YES', 1,0)) auto_extens_c
FROM cdb_DATA_FILES
GROUP BY TABLESPACE_NAME
, con_id
) d
, (
SELECT TABLESPACE_NAME
, SUM (nvl (BYTES, 0)) / 1024 FREE_SPACE
, con_id
FROM cdb_FREE_SPACE
GROUP BY TABLESPACE_NAME
, con_id
) f
WHERE d.TABLESPACE_NAME = f.TABLESPACE_NAME
AND d.con_id = f.con_id
AND ts.TABLESPACE_NAME = d.TABLESPACE_NAME
AND ts.con_id = d.con_id
AND ts.con_id = x.con_id (+)
