How to move datafiles in temp tablespace?

One can’t move the temp tablespace during mount stage like other datafiles using “ALTER DATABASE RENAME FILE..” so a workaround this issue is to create a new temp tablespace.

SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

— create a new temp tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/

— change default temporary tablespace
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

— drop old temp tablespace
SQL> drop tablespace temp including contents and datafiles;

— recreate the temp with new file location
SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u03/oradata/TESTDB/temp_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/

— make the temp default again
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

— drop temp2 tablespace
SQL> drop tablespace temp2 including contents and datafiles;

2 thoughts on “How to move datafiles in temp tablespace?

    • The database has to be in the mount stage to get list of datafiles as the controlfile has the list of the datafiles. When the database is mount it reads the control file. At nomount state the database has only read the pfile/spfile.

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