12c, CDB, PDB, tablespaces

Managing tablespaces in CDB and PDB

Leave a comment

An Oracle 12c multitenant database consist of a CDB which houses one to many PDBs serving end user applications.    Thus objects such as tablespaces can become a little confusing to manage with questions of do they belong to CDB or PDB or both.   This article tries to clear this subject and give a brief tutorial in managing tablespaces in the multitenant architecture.

 

Rules to tablespaces in CDB and PDB:

 

  1. Permanent tablespace can only be associated with a single container (CDB or PDB).
  2. When you create a tablespace in a container, it belongs to that container.
  3. CDB can have only one active undo tablespace or one active undo tablespace for each instance of RAC.
  4. Only one default temporary tablespace for CDB.
  5. PDBs can use the CDB’s temporary tablespace.
  6. Each PDB can have its own default temporary tablespace.
  7. PDBs can have their own tablespaces.
  8. Permanent Default tablespace in PDB can still use the ALTER DATABASE command, but it is preferred to use the command:

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE tbs_user_pdb;

  1. CDB will have only one temporary tablespace.
  2. To modify the temporary tablespace for CDB, the current container must be root.
  3. You can have other temporary tablespaces in CDB with specific users assigned to them, but only one default.
  4. Each PDB can have only one temporary tablespace and when PDB is unplugged so is its temporary tablespace.

 

 

 

Managing tablespaces in Multitenant architecture.

 

  1. Logon as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Oct 25 09:29:48 EDT 2015 on pts/1

[oracle@linux2 ~]$

 

  1. Connect to your CDB with sqlplus and show current container.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 26 10:02:53 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> show con_name

 

CON_NAME

——————————

CDB$ROOT

SQL>

 

  1. Create a new permanent tablespace in the root container of cdb1_user, set it as the default tablespace for root.

 

SQL> create tablespace cdb1_users

datafile ‘/opt/app/oradata/cdb1_users.dbf’ size 10m;  2

 

Tablespace created.

 

SQL> ALTER DATABASE DEFAULT TABLESPACE cdb1_users;

 

Database altered.

 

SQL>

 

  1. View tablespaces from the root container and default tablespaces.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

CDB1_USERS

 

6 rows selected.

 

SQL> select distinct(default_tablespace) from dba_users;

 

DEFAULT_TABLESPACE

——————————

SYSAUX

CDB1_USERS

SYSTEM

 

 

  1. Change to one of your pluggable database and note that you cannot see the newly create tablespace cdb1_users.

 

SQL> alter session set container=pdb1;

 

Session altered.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

TEMP

USERS

 

SQL>

 

  1. Create a tablespace tablespace in your PDB and move back to your root container.

 

SQL> create tablespace pdb1_users

datafile ‘/opt/app/oradata/pdb1_users.dbf’ size 10m;  2

 

Tablespace created.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

TEMP

USERS

PDB1_USERS

 

SQL> alter session set container=CDB$ROOT;

 

Session altered.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

CDB1_USERS

 

6 rows selected.

 

SQL>

 

 

  1. This completes the management of tablespaces within the multitenant database architecture.

 

Larry Catt

OCP

Leave a Reply