How to Rename TempFile in Oracle

How to Rename TempFile in Oracle

The procedure of renaming an Oracle tempfile is same as the procedure of moving tempfile with a different file name.

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 datafiles.

set pages 1000 lines 1000;

column FILE_NAME format a70;

select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;

column name format a70;

select name, status from v$tempfile;

Find the tablespace owned by tempfile 

select t.name tablespace_name from v$tempfile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_kh3nttq6_.tmp');

Follow the below steps to rename a tempfile.

a. Offline TempFile

b. Move TempFile

c. Rename TempFile

d. Online Tempfile

4. Offline TempFile

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

5. Move TempFile

cd /u01/app/oracle/oradata/DEVDB/datafile/

mv o1_mf_temp_kh3nttq6_.tmp temp_01.tmp

ls -ltrh

6. Rename TempFile

alter database rename file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_kh3nttq6_.tmp' to '/u01/app/oracle/oradata/DEVDB/datafile/temp_01.tmp';

7. Online Tempfile

alter database tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp_01.tmp' online;

Bonus Topic:

Alter Temporary Tablespace Correctly:

You can't create a temporary tablespace like a normal, permanent tablespace.

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

Or

create tablespace TEMP01 tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp01.dbf' size 10M 

autoextend on next 10M maxsize unlimited, '/u01/app/oracle/oradata/DEVDB/datafile/temp02.dbf' size 10M autoextend on next 10M maxsize unlimited;

You have to use CREATE TEMPORARY TABLESPACE to explicitly declare the nature of the tablespace.

create temporary tablespace TEMP01 tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp01.dbf' size 10M autoextend on next 10M maxsize unlimited;

ORA-00940: invalid ALTER command

In order to add new tempfile to temporory tablesoace we should not use keyworld temporary TABLESPACE, We can just add link normal datafile to normal TABLESPACE.

This below command will fail:

alter temporary tablespace TEMP01 add tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Or try to offline a temporary tablespace which will also fails

alter temporary tablespace temp offline;

Correct method of of alter a Temporary Tablespace is:

Since the database has already knew the nature of a named tablespace, we don't and can't specify TEMPORARY to alter the temporary tablespace

alter tablespace TEMP01 add tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp02.dbf' size 10m autoextend on next 10m maxsize unlimited;

How to Alter Temporary Tablespace Offline:

Theoretically, you can't take the whole temporary tablespaces offline. (ORA-03217: invalid option for alter of TEMPORARY TABLESPACE)

Below command will fail

alter tablespace temp offline;

ORA-03217 told us that we can't take a whole temporary tablespace offline. This is true. According to Oracle documentation, we see something about taking a temporary tablespace offline.

Offline a Tempfile

Instead, we can take one tempfile offline at a time by ALTER DATABASE.

column name format a70;

select name, status from v$tempfile;

alter database tempfile '/u01/app/oracle/oradata/DEVDB/datafile/temp01.dbf' offline;

Offline All Tempfiles

We cannot offline all tempfiles of the default temporary tablespace.

alter tablespace TEMP01 tempfile offline; >>> This will fail 

If the temporary tablespace is not the default one. We can take all temp files offline in the tablespace like this:

alter tablespace TEMP02 tempfile offline; >>> This will work 

select name, status from v$tempfile;

select tablespace_name, status from dba_tablespaces where tablespace_name in ('TEMP01', 'TEMP02');

Regards,

Mallik