8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article demonstrates how to manage tablespaces in a container database (CDB) and pluggable database (PDB).
- Manage Tablespaces in a CDB
- Manage Tablespaces in a PDB
- Undo Tablespaces
- Temporary Tablespaces
- Default Tablespaces
Related articles.
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;
With both methods, you should be pointing to the appropriate container for the command to work.
For more information see:
- Multitenant : All Articles
- Multitenant : Manage Tablespaces in a CDB and a PDB
- Introduction to the Multitenant Architecture
- Overview of the Multitenant Architecture
- Managing a Multitenant Environment
- CREATE TABLESPACE
- ALTER TABLESPACE
- DROP TABLESPACE
- ALTER PLUGGABLE DATABASE
Hope this helps. Regards Tim...