How to change the Temporary Tablespace File Location in 12c

As we know there are 1 or more Temporary Tablespaces in 12c, considering the fact, every container has its own Temp Tablespace.

So in our case we have – 1 CDB (Container Database) with 1 PDB (Pluggable Database) and both have same name as TEMP.

Lets start with the Root Container Temporary Tablespace File Location Change. We cant use – ALTER DATABASE MOVE DATAFILE command to change it as it does not work for Temporary Tablespaces, so we have to create a new Temp Tablespace all together to achieve the target.

So what are steps to be done:

Step 1:

  • First create a New Temp Tablespace (temp2) with different file name
  • Make this New Temp Tablespace (temp2) as Default One
  • Remove the Old Temporary Tablespace (temp) along with the Temporary File.

If we are happy with the name change of the Temporary Tablespace, we can stop here, however if we want the same name as before we must follow step 2.

Step 2:

  • Create the Temp tablespace with Name Temp, which was the old name
  • Make this Tablespace as a Default Temporary Tablespace.
  • Remove the Temporary Tablespace (temp2) along with the Temporary File.

Now follow the steps, first connect to root container CDB$ROOT:

SQL> alter session set container=cdb$root;

Session altered.

SQL>

Find out the name of the Temporary Tablespace along with the File Location:

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————-
/u01/app/oracle/oradata/CDB2/datafile/o1_mf_temp_cgf7hvo1_.tmp

SQL>

It still shows CDB2, because this DATABASE was renamed from CDB2 to CDB3, so it is still in the old directory.

As we already created the required directory while moving the other datafiles for cdb3, we will just go ahead with the new name for the Temporary Tablespace. After we create the tablespace we will make this new tablespace as a default one, because we cant remove the TEMP Tablespace unless we create an other one and make it as default.

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oracle/oradata/cdb3/datafile/pdb1_temp01.dbf‘ SIZE 500M
2 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>

Now check what is the default tablespace now:

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP2

SQL>

Now our work is done, we have changed the location of Temporary Tablespace and name is also changed to Temp2 from Temp. However to keep the same as before we have to do this exercise one more time and make TEMP as default one.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/u01/app/oracle/oradata/cdb3/datafile/pdb1_temp1.dbf‘ SIZE 500M
2 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP

SQL>

So, we have Temp as a name now along with the change of file.

Now we will change the Location for Pluggable Database:

First connect to PDB and find out the Temporary Tablespace Name and Locaion of Temp File:

SQL> alter session set container=pdb1;

Session altered.

SQL>

Find out the name of Temp Tablespace:

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP

SQL>
SQL> SELECT file_name, bytes, status, autoextensible FROM dba_temp_files;

FILE_NAME BYTES STATUS AUT
———————————————————————————————– ———- ——- —
/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_temp_cgf7wwc9_.dbf 20971520 ONLINE YES

SQL>

So if you noticed we have same name temporary tablespace in Container as well in the Pluggable Database.

Now create a temp tablespace (temp2), make it as default and remove the old one (temp).

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_temp01.dbf’ SIZE 500M
2 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL>
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL>
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP2

SQL>

Now its time to go back to temp tablespace, so create a temp tablespace and make it as default and remove temp2:

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_temp1.dbf‘ SIZE 500M
2 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE
—————————— ——————————
DEFAULT_TEMP_TABLESPACE TEMP

SQL>

So we are back with temp name for container and pluggable database.

If any one has any questions, let me know.

 

Advertisements

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