Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle

Manage Tablespaces in a CDB

Management of tablespaces in a container database (CDB) is no different to that of a non-CDB database. Provided you are logged in as a privileged user and pointing to the root container, the usual commands are all available.

CONN / AS SYSDBA

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL>

CREATE TABLESPACE dummy
  DATAFILE '/u01/app/oracle/oradata/cdb1/dummy01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
 
Tablespace created.

SQL>

ALTER TABLESPACE dummy ADD
  DATAFILE '/u01/app/oracle/oradata/cdb1/dummy02.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;

Tablespace altered.

SQL>

DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>

Manage Tablespaces in a PDB

The same tablespace management commands are available from a pluggable database (PDB), provided you are pointing to the correct container. You can connect using a common user then switch to the correct container.

SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL>
Alternatively, connect directly to the PDB as a local user with sufficient privilege.

SQL> CONN pdb_admin@pdb1
Enter password:
Connected.
SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL>
Once pointed to the correct container, tablespaces can be managed using the same commands you have always used. Make sure you put the datafiles in a suitable location for the PDB.

CREATE TABLESPACE dummy
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
 
Tablespace created.

SQL>

ALTER TABLESPACE dummy ADD
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy02.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;

Tablespace altered.

SQL>

DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>


Undo Tablespaces

Management of the undo tablespace in a CDB is unchanged from that of a non-CDB database.

In contrast, a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible.

CONN pdb_admin@pdb1

SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS

SQL>

But we can see the datafile associated with the CDB undo tablespace.

SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

SQL>

SELECT name FROM v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/temp01.dbf

SQL>


Temporary Tablespaces

Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database.

A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.

CONN pdb_admin@pdb1

CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/app/oracle/oradata/cdb1/pdb1/temp02.dbf' SIZE 5M
  AUTOEXTEND ON NEXT 1M;
 
Tablespace created.

SQL>

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>


Default Tablespaces

Setting the default tablespace and default temporary tablespace for a CDB is unchanged compared to a non-CDB database.

There are a two ways to set the default tablespace and default temporary tablespace for a PDB. The ALTER PLUGGABLE DATABASE command is the recommended way.

CONN pdb_admin@pdb1
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;
For backwards compatibility, it is also possible to use the ALTER DATABASE command.

CONN pdb_admin@pdb1
ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;