-- WX:DBAJOE399 --
set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (GB)" format 9,999,990.00
column "ALLOC (GB)" format 9,999,990.00
column "USED (GB)" format 9,999,990.00
select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/power(2,30) "MAXSIZE (GB)",nvl(a.physical_bytes,0)/power(2,30) "ALLOC (GB)" ,nvl(b.tot_used,0)/power(2,30) "USED (GB)"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_data_files group by con_id,tablespace_name ) a,
(select con_id,tablespace_name, sum(bytes) tot_used from cdb_segments group by con_id,tablespace_name ) b,
(select name,con_id from v$containers) c
where a.con_id= b.con_id and a.con_id = c.con_id and a.tablespace_name = b.tablespace_name (+)
order by 1,3;
-- output
CON_ID NAME TABLESPACE_NAME MAXSIZE (GB) ALLOC (GB) USED (GB)
------ -------- --------------- ------------ ---------- ---------
1 CDB$ROOT SYSAUX 32,767.98 510.00 479.00
1 CDB$ROOT SYSTEM 32,767.98 840.00 829.88
1 CDB$ROOT UNDOTBS1 32,767.98 60.00 9.06
3 XEPDB1 SYSAUX 32,767.98 410.00 383.31
3 XEPDB1 SYSTEM 32,767.98 260.00 255.94
3 XEPDB1 UNDOTBS1 32,767.98 100.00 0.00
4 PDB2 SYSAUX 32,767.98 410.00 383.44
4 PDB2 SYSTEM 32,767.98 260.00 255.94
4 PDB2 UNDOTBS1 32,767.98 100.00 0.00
-- WX:DBAJOE399 --
set line 200 pages 999
column name for a10
column tablespace_name for a26
column "MAXSIZE (GB)" format 9,999,990.00
column "ALLOC (GB)" format 9,999,990.00
select a.con_id,c.name,a.tablespace_name,a.bytes_alloc/power(2,30) "MAXSIZE (GB)",nvl(a.physical_bytes,0)/power(2,30) "ALLOC (GB)"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_temp_files group by con_id,tablespace_name ) a,
(select name,con_id from v$containers) c
where a.con_id = c.con_id
order by 1,3;
--output
CON_ID NAME TABLESPACE_NAME MAXSIZE (GB) ALLOC (GB)
------ -------- --------------- ------------ ----------
1 CDB$ROOT TEMP 32,767.98 131.00
3 XEPDB1 TEMP 32,767.98 129.00
4 PDB2 TEMP 32,767.98 129.00
