Tag Archives: oracle 12c

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