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

Check the size of datafiles in CDB & PDB database with total bytes allocated and used in Oracle

set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (MB)" format 9,999,990.00
column "ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)",nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(1024*1024) "USED (MB)"
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;

CON_ID NAME     TABLESPACE_NAME MAXSIZE (MB) ALLOC (MB) USED (MB)
------ -------- --------------- ------------ ---------- ---------
     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

Check the size of temp tablespace in CDB and PDB databases

set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (MB)" format 9,999,990.00
column "ALLOC (MB)" format 9,999,990.00
select a.con_id,c.name,a.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)",nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)"
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;

CON_ID NAME     TABLESPACE_NAME MAXSIZE (MB) ALLOC (MB)
------ -------- --------------- ------------ ----------
     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

Check size in GB format of any tablespace

select con_id,tablespace_name, sum(bytes)/1024/1024/1024 physical_gb,
sum(decode(autoextensible,'NO',bytes/1024/1024/1024,'YES',maxbytes/1024/1024/1024)) GB_alloc
from CDB_DATA_FILES group by tablespace_name,con_id;


Check size of Segment / object in GB

select con_id,tablespace_name, sum(bytes)/1024/1024/1024 tot_used from cdb_segments group by con_id,tablespace_name;


(nvl(b.tot_used,0)/a.bytes_alloc)*100 “PERC_USED”

Check size used percentage

set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (MB)" format 9,999,990.00
column "ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "PERC_USED" format 99.00
select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)",
nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(1024*1024) "USED (MB)" ,(nvl(b.tot_used,0)/a.bytes_alloc)*100 "PERC_USED"
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;

CON_ID NAME     TABLESPACE_NAME MAXSIZE (MB) ALLOC (MB) USED (MB) PERC_USED
------ -------- --------------- ------------ ---------- --------- ---------
     1 CDB$ROOT SYSAUX             32,767.98     510.00    479.00      1.46
     1 CDB$ROOT SYSTEM             32,767.98     840.00    829.88      2.53
     1 CDB$ROOT UNDOTBS1           32,767.98      60.00      9.06       .03
     3 XEPDB1   SYSAUX             32,767.98     410.00    383.44      1.17
     3 XEPDB1   SYSTEM             32,767.98     260.00    255.94       .78
     3 XEPDB1   UNDOTBS1           32,767.98     100.00      0.00       .00
     4 PDB2     SYSAUX             32,767.98     410.00    383.50      1.17
     4 PDB2     SYSTEM             32,767.98     260.00    255.94       .78
     4 PDB2     UNDOTBS1           32,767.98     100.00      0.00       .00