Tablespace information in Multitenant for CDB and all PDBs

Check the tablespace information for the CDB and all PDBs in open state.

SET LINES 200 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 MB"
COL apm FORM 999,999,999,999 HEAD "Alloc|Space MB"
COL maxalloc FORM 999,999,999,999 HEAD "MAXALLOCATE MB"
---
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, maxbytes/1024/1024 maxalloc
FROM cdb_data_files cd
,v$containers c2
WHERE cd.con_id = c2.con_id
GROUP BY c2.con_id
,cd.tablespace_name,maxbytes/1024/1024 )
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm,y.maxalloc
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,tf.maxbytes/1024/1024 maxalloc
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,tf.maxbytes/1024/1024
ORDER BY 1, 2;

Note: If tablespace maxbytes having value 0 that means its not defined at time of creation and auto extend of file is NO. Verified with following command:

SQL> select bytes,maxbytes,autoextensible,tablespace_name,con_id from cdb_data_files
;

     BYTES   MAXBYTES AUT TABLESPACE_NAME     CON_ID
---------- ---------- --- --------------- ----------
   5242880 3.4360E+10 YES USERS                    1
  68157440 3.4360E+10 YES UNDOTBS1                 1
 650117120 3.4360E+10 YES SYSAUX                   1
 943718400 3.4360E+10 YES SYSTEM                   1
   5242880 3.4360E+10 YES USERS                    4
 104857600 3.4360E+10 YES UNDOTBS1                 4
 419430400 3.4360E+10 YES SYSAUX                   4
 272629760 3.4360E+10 YES SYSTEM                   4
 304087040 3.4360E+10 YES SYSTEM                   5
 492830720 3.4360E+10 YES SYSAUX                   5
 445644800 3.4360E+10 YES UNDOTBS1                 5
   5242880 3.4360E+10 YES USERS                    5
 104857600          0 NO  TEST_TB                  5

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.