Tablespace Utilization In Oracle Multitenant Database (CDB & PDB)

In this article, we will check tablespace utilization in CDB and PDB databases as you know oracle introduced a multitenant architecture from the Oracle 12c version.

New views have been introduced in 12c multitenant architecture for checking the size of datafiles and tempfiles that are used in CDB or PDB in the Oracle database. We have used these views in the below script for monitoring tablespace usage within Oracle.

cdb_data_files
cdb_segments
v$containers

Table of Contents

Tablespace Utilization In Oracle

Use the below scripts to monitor Tablespace in Oracle CDB and PDB databases.

Monitor tablespace utilization script for oracle 12c and higher versions

The below script only works in the case of CDB and PDB configuration, run the script and find tablespace utilization.

SQL> set pages 80
 set lin 120
 set echo off
 set feed off
 column PCT format 999.99
 column tablespace format A30
 col container for a30

 select substr(t.tablespace_name,1,30) tablespace,
      round(t.tsbytes/(10241024),0) "ALLOCATED(MB)",      round(nvl(s.segbytes,0)/(10241024),0) "USED(MB)",
      round((nvl(s.segbytes,0)/t.tsbytes)*100,2) PCT,
      lower(vc.name) as container
 from
    (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) t,
    (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
    v$containers vc
 where t.con_id=s.con_id(+)
   and t.tablespace_name=s.tablespace_name(+)
   and t.con_id=vc.con_id
 order by container, tablespace;

Script to check usage percentage in CDP and PDB database

The below script will show you tablespace utilization with percentage.

SQL> 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/(10241024) "MAXSIZE (MB)", nvl(a.physical_bytes,0)/(10241024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(10241024) "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;

Script to check utilized space in CDB and PDB

Check total used space by CDB.

SQL>select sum(BYTES)/1024/1024/1024 from cdb_segments;

OR

SQL> select CON_ID,sum(BYTES)/1024/1024/1024 from cdb_segments group by CON_ID;

Check Datafile size in CDB

The below command will help us to find datafile size in the CDB configuration.

SQL> col FILE_NAME for a55
  set lines 200
 select FILE_NAME,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from cdb_data_files where TABLESPACE_NAME='OCP_TECH';