A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Managing Tablespace in CDB and PDB

3 min read
pile of cylinder silver keg tank lot

Photo by ELEVATE on Pexels.com

Managing tablespace in container database is similar to non-container database. Provided you are logged in as a privileged user and point to right container.

Manage Tablespace in CDB

SQL> show con_name
 
CON_NAME
------------------------------
CDB$ROOT
 
SQL> CREATE TABLESPACE CDB_TABLESPACE DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;
 
Tablespace created.
 
SQL> ALTER TABLESPACE CDB_TABLESPACE ADD DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;
 
Tablespace altered.
 
SQL> select TABLESPACE_NAME, FILE_NAME from cdb_data_files where con_id=1;
 
TABLESPACE_NAME      FILE_NAME
-------------------- ------------------------------------------------------------
SYSTEM               +ASM/CSHARK/DATAFILE/system1
SYSAUX               +ASM/CSHARK/DATAFILE/sysaux1
UNDOTBS1             +ASM/CSHARK/DATAFILE/undotbs1
USERS                +ASM/CSHARK/DATAFILE/users1
CDB_TABLESPACE       +ASM/CSHARK/DATAFILE/cdb_tablespace1
CDB_TABLESPACE       +ASM/CSHARK/DATAFILE/cdb_tablespace2
 
6 rows selected.
 
SQL> DROP TABLESPACE CDB_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
 
Tablespace dropped.

Managing Tablespace in PDB

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 SHARK                          READ WRITE NO
SQL> ALTER SESSION SET CONTAINER=SHARK;
 
Session altered.
 
SQL>
SQL> show con_name
 
CON_NAME
------------------------------
SHARK
 
SQL> CREATE TABLESPACE PDB_TABLESPACE DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;
 
Tablespace created.
 
SQL> ALTER TABLESPACE PDB_TABLESPACE ADD DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;
 
Tablespace altered.
 
SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM CDB_DATA_FILES WHERE TABLESPACE_NAME='PDB_TABLESPACE';
 
TABLESPACE_NAME      FILE_NAME
-------------------- ------------------------------------------------------------------------------------------
PDB_TABLESPACE       +ASM/CSHARK/ED7B4B/DATAFILE/pdb_tablespace1
PDB_TABLESPACE       +ASM/CSHARK/ED7B3C/DATAFILE/pdb_tablespace2
 
 
SQL> DROP TABLESPACE PDB_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
 
Tablespace dropped.
Tablespace info in Multitenant:
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, f1.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;
 
Temp tablespace details in Multitenant:
set lines 300 pages 300
col name format a10
col FILE_NAME format a90
select a.name,b.FILE_ID,b.tablespace_name,b.file_name from V$CONTAINERS a , CDB_TEMP_FILES b where a.con_id=b.con_id;
Check to undo mode in Multitenant DB:
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
 
PROPERTY_NAME
--------------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------------DESCRIPTION
--------------------------------------------------------------------------------------LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled