Manage Temporary Tablespace in a 12cR1 Container Database (CDB) and Pluggable Database (PDB)

By default, both CDB and PDB have a TEMP tablespace:

SQL> show con_id con_name

CON_ID
------------------------------
1

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

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
------------------------------
3

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

SQL> select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                            DESCRIPTION
---------------------------------------- ---------------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                  OLTP_TEMP                                Name of default temporary tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

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

SQL> select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                           DESCRIPTION
---------------------------------------- ---------------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP                                     Name of default temporary tablespace
Advertisements
This entry was posted in 12c new features, CDB/PDB and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s