In this article, we will see how tablespaces are managed in a Multi-tenant architecture.
Mainly we have 4 types of tablespaces
- System Tablespace.
- User permanent tablespace.
- Temporary tablespace.
- 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
