How to Move Temp File Location in Oracle

How to Move Temp File Location in Oracle

Standalone Database:

1. Connect to the database.

. oraenv

sqlplus / as sysdba

2. Check the Database Status.

select name, open_mode from v$database;

select instance_name, status from v$instance;

3. Check current tempfiles.

column name format a70;

select name, status from v$tempfile;

4. Add a new tempfile (Example - Optional step)

select name from v$tablespace;

alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp01.dbf' size 10M autoextend on next 10M maxsize unlimited;

Check the status of tempfile status

select name, status from v$tempfile;

OK, Both old and new tempfile are online.

5. Offline the original/old tempfile.

alter database tempfile '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_kh3nttq6_.tmp' offline;

6. Drop the original tempfile.

Drop the tempfile physically.

alter database tempfile '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_kh3nttq6_.tmp' drop including datafiles;

Check current tempfiles again.

select name, status from v$tempfile;

RAC Database:

1. Connect to the database.

. oraenv

sqlplus / as sysdba

2. Check the Database Status.

select name, open_mode from v$database;

select instance_name, status from v$instance;

3. Check current tempfiles.

column name format a70;

select name, status from v$tempfile;

4. Add a new tempfile (Example - Optinal step)

Add a new tempfile.

alter tablespace temp add tempfile '+DATA/DEVCDB/datafile/temp02.dbf' size 10M autoextend on next 10M maxsize unlimited;

select name, status from v$tempfile;

5. Offline the original tempfile.

alter database tempfile '+DATA/DEVCDB/datafile/temp01.dbf' offline;

6. Drop the original tempfile.

alter database tempfile '+DATA/DEVCDB/datafile/temp01.dbf' drop including datafiles;

Regards,

Mallik