Oracle Database 12c New Features

Tablespaces in multitenancy

Note:

  • In a non-CDB, all the tablespaces belong to one database. 
  • In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces. 
  • Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links. 
  • There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.
  • The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
  2  FROM cdb_tablespaces t, cdb_pdbs p
  3  WHERE t.con_id = p.pdb_id(+)
  4  ORDER BY 2,1;

TABLESPACE_NAME                PDB_NAME
------------------------------ --------------------
SYSAUX                         PDB$SEED
SYSTEM                         PDB$SEED
TEMP                           PDB$SEED
SYSAUX                         PDB2
SYSTEM                         PDB2
TEMP                           PDB2
SYSAUX                         ROOT
SYSTEM                         ROOT
TEMP                           ROOT
UNDOTBS1                       ROOT
UNDOTBS2                       ROOT
USERS                          ROOT

12 rows selected.

The CREATE TABLESPACE command should be familiar. The change in its behavior in a CDB is that the tablespace is created in the container where the command is executed. Separating the data files into different directories by PDB can help determine which files belong to which PDB, though it is not necessary but it is good from administration perspective.

Create tablespace at CDB

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
  2  DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
  2  DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.