LESSON 15
How to manage tablespaces in a CDB (Multitenant Container) and PDBs
(Pluggable Databases)
Let us view permanent
and temporary tablespaces properties in mycdb
using the DATABASE_PROPERTIES view.
Login to mycdb
SQL> connect /
as sysdba
SQL> SELECT
property_value, property_name
2 FROM
database_properties
3 WHERE
property_name LIKE "DEFAULT%TABLE%";
PROPERTY_VALUE |
PROPERTY_NAME |
TEMP |
DEFAULT_TEMP_TABLESPACE |
USERS |
DEFAULT_PERMANENT_TABLESPACE |
List all tablespaces in
your CDB.
SQL> SELECT
con_id, tablespace_name FROM cdb_tablespaces ORDER by 1;
CON_ID |
TABLESPACE_NAME |
1 |
SYSTEM |
1 |
SYSAUX |
1 |
UNDOTBS1 |
1 |
TEMP |
2 |
SYSTEM |
2 |
SYSAUX |
3 |
SYSTEM |
3 |
SYSAUX |
4 |
SYSTEM |
4 |
SYSAUX |
5 |
SYSTEM |
5 |
SYSAUX |
5 |
TEMP |
5 |
USERS |
6 |
SYSTEM |
6 |
SYSAUX |
6 |
TEMP |
6 |
USERS |
6 |
EXAMPLE |
Notice that you have
repeating SYSTEM and SYSAUX in all the containers. Also, you can have as
many tablespaces on any pluggable databases.
For example, you may
have the TEMP tablespace in all PDBs.
List all tablespaces in
your CDB that are using the TEMP tablespace.
SQL> SELECT
con_id, tablespace_name FROM cdb_tablespaces
2 WHERE
tablespace_name = "TEMP"
3 ORDER
by 1;
CON_ID |
TABLESPACE_NAME |
1 |
TEMP |
2 |
TEMP |
3 |
TEMP |
4 |
TEMP |
5 |
TEMP |
6 |
TEMP |
All PDBs have the TEMP
tablespace.
Create a tablespace (mytbs)
in the root container.
SQL> CREATE
TABLESPACE mytbs
2
DATAFILE "/oradata/mycdb/mytbs01.dbf"
3 SIZE
150M;
List the tablespaces in
your CDB.
SQL> SELECT
con_id, tablespace_name FROM cdb_tablespaces
2 WHERE
tablespace_name = "MYTBS";
CON_ID |
TABLESPACE_NAME |
1 |
MYTBS |
Notice that the con_id
value is 1. That means the tablespace was created the root container.
Change your default
tablespace from USERS to MYTBS.
SQL> ALTER
DATABASE DEFAULT TABLESPACE mytbs;
List your default
tablespaces.
SQL> SELECT
property_value, property_name
2 FROM
database_properties
3 WHERE
property_name LIKE "DEFAULT%TABLE%";
PROPERTY_VALUE |
PROPERTY_NAME |
TEMP |
DEFAULT_TEMP_TABLESPACE |
MYTBS |
DEFAULT_PERMANENT_TABLESPACE |
Notice that your default
permanent tablespace was changed to MYTBS
Now, let us create a
permanent tablespace (mytbs4local) in one
of your pluggable database (mypdb1).
First, you need to login
to the mypdb1 pluggable database.
SQL> connect
sys/mypass@mypdb1 as sysdba
Connected.
Create a tablespace
(mytbs4local) in mypdb1.
SQL> CREATE
TABLESPACE mytbs4local
2
DATAFILE "/oradata/mycdb/mypdb1/mytbs4local01.dbf"
3 SIZE
150M;
Tablespace created.
Make the mytbs4local
tablespace the default tablespace in the mypdb1 container.
SQL> ALTER
PLUGGABLE DATABASE DEFAULT TABLESPACE mytbs4local;
Pluggable database
altered.
List your default
tablespaces in the mypdb1 container.
SQL> SELECT
property_value, property_name
2 FROM
database_properties
3 WHERE
property_name LIKE "DEFAULT%TABLE%";
PROPERTY_VALUE |
PROPERTY_NAME |
TEMP |
DEFAULT_TEMP_TABLESPACE |
MYTBS4LOCAL |
DEFAULT_PERMANENT_TABLESPACE |
Notice that your default
permanent tablespace is MYTBS4LOCAL in
the mypdb1 container.
Create a temporary
tablespace (temp4mypdb1) in the
mypdb1 container.
SQL> CREATE
TEMPORARY TABLESPACE temp4mypdb1
2
TEMPFILE "/oradata/mycdb/mypdb1/temp4mypdb1_01.dbf"
3 SIZE
200M;
Tablespace created.
List your default
tablespaces in the mypdb1 container.
SQL> SELECT
property_value, property_name
2 FROM
database_properties
3 WHERE
property_name LIKE "DEFAULT%TABLE%";
PROPERTY_VALUE |
PROPERTY_NAME |
TEMP4MYPDB1 |
DEFAULT_TEMP_TABLESPACE |
MYTBS4LOCAL |
DEFAULT_PERMANENT_TABLESPACE |
Notice that your default
permanent tablespace is MYTBS4LOCAL and
its default temporary tablespace is TEMP4MYPDB1
in the mypdb1 container.
Good Luck!
|