In this article, we will see how tablespaces are managed in a Multi-tenant architecture.

Mainly we have 4 types of tablespaces

  1. System Tablespace.
  2. User permanent tablespace.
  3. Temporary tablespace.
  4. Undo tablespace.

System tablespace must be created while creating a database using either DBCA or manually.

SQL> select name,con_id from v$datafile where name like '%system%';

NAME												          CON_ID
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
/u02/oradata/testcdb/system01.dbf								       1
/u02/oradata/testcdb/pdbseed/system01.dbf							       2
/u02/oradata/testcdb/testpdb1/system01.dbf							       3
/u02/oradata/TESTCDB/71DA02D769265585E055000000000001/datafile/o1_mf_system_fokrl9lc_.dbf	       4
/u02/oradata/TESTCDB/7216B23DC4065C7CE055000000000001/datafile/o1_mf_system_fosq3dlk_.dbf	       5
/u02/oradata/TESTCDB/7280FBAEFC68381BE055000000000001/datafile/o1_mf_system_fp7s1dn7_.dbf	       7
/u02/oradata/TESTCDB/728C0D40E7902D23E055000000000001/datafile/o1_mf_system_fp93brf2_.dbf	       8

We can see in above output system tablespace is created for each container database.

User Permanent tablespace can be created by giving user_data tablespace clause while creating the database. And by using CREATE TABLESPACE command by connecting into particular CDB or PDB.

Let us understand it by example :

Create datafile in CDB$ROOT

Step 1: Connect to CDB$ROOT and check connected container using show con_name command

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Step 2: Create the u_data tablespace

SQL> CREATE TABLESPACE U_DATA DATAFILE SIZE 20M;

Tablespace created.

Step 3: check datafile in v$datafile view

SQL>  select name ,con_id from v$datafile where name like '%u_data%';

NAME													 CON_ID
---------------------------------------------------------------------------------------------------- ----------
/u02/oradata/TESTCDB/datafile/o1_mf_u_data_fpmx0fhp_.dbf						      1

We can see here u_data datafile is created under con_id 1 that is CDB$ROOT.

Create datafile in Pluggable database 

Step 1: Connect to a pluggable database

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.
SQL> show con_name

CON_NAME
------------------------------
PDB1

Step 2: Create a tablespace

SQL> CREATE TABLESPACE T_DATA DATAFILE SIZE 300M;

Tablespace created.

Step 3: Check v$datafile view

SQL> SELECT NAME,CON_ID FROM V$DATAFILE WHERE NAME LIKE '%t_data%';

NAME													 CON_ID
---------------------------------------------------------------------------------------------------- ----------
/u02/oradata/TESTCDB/7280FBAEFC68381BE055000000000001/datafile/o1_mf_t_data_fpmxdoxw_.dbf		      7

We can see here datafile is created under con_id 7 which is a PDB1 database.

Setting default user tablespace database.

At CDB level

Connect to CDB$ROOT

SQL> alter session set container=CDB$ROOT;

Session altered.

Use alter database to change default tablespace

SQL> alter database default tablespace users;

Database altered.

At PDB level

Connect to the PDB database :

SQL> alter session set container=PDB1;

Session altered.

Use alter pluggable database statement to change default tablespace :

SQL> alter pluggable database default tablespace users;

Pluggable database altered.

In the next article, we will be managing temporary and undo tablespace in CDB and PDB.

Stay tuned for More articles on Oracle Multitenant

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.