Tablespace Monitoring In Oracle Multitenant Database

 

Tablespace Monitoring In Oracle 12c Multitenant Database.

You can use this script to check tablespace space details in 12c Multitenant database. It will provide information of both root CDB and PDB.

SET LINES 132 PAGES 100

COL con_name FORM A15 HEAD "Container|Name"

COL tablespace_name FORM A15

COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg."

COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg."

--

COMPUTE SUM OF fsm apm ON REPORT

BREAK ON REPORT ON con_id ON con_name ON tablespace_name

--

WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm

FROM cdb_free_space cf1

,v$containers c1

WHERE cf1.con_id = c1.con_id

GROUP BY c1.con_id, cf1.tablespace_name),

y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm

FROM cdb_data_files cd

,v$containers c2

WHERE cd.con_id = c2.con_id

GROUP BY c2.con_id

,cd.tablespace_name)

SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm

FROM x, y, v$containers v

WHERE x.con_id = y.con_id

AND x.tablespace_name = y.tablespace_name

AND v.con_id = y.con_id

UNION

SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024

FROM v$containers vc2, cdb_temp_files tf

WHERE vc2.con_id = tf.con_id

GROUP BY vc2.con_id, vc2.name, tf.tablespace_name

ORDER BY 1, 2;

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;


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