【Oracle】Check size of datafiles and tempfile tablespaces used in CDB and PDB

 

 

 

-- 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