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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.