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).
Related articles.
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>
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>
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>
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>
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:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/12c/multitenant-manage-tablespaces-in-a-cdb-and-pdb-12cr1