How To Move Or Rename The Tempfile In Oracle

 Log in SYSDBA

 Check  Tempfile  status

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                         STATUS
---------- -------------------------         -------
 1         /appsolworld/temp01.dbf           ONLINE
 2         /appsolworld/temp02.dbf           ONLINE
 3         /appsolworld/temp03.dbf           ONLINE

 Make it OFFLINE the tempfile that has to move

SQL> ALTER DATABASE TEMPFILE '/appsolworld/temp01.dbf' OFFLINE;
We should make offline the tempfile that has to move, if not then will get this error :
SQL> ALTER DATABASE RENAME FILE '/appsolworld/temp01.dbf' TO '/u02/temp01.dbf';
ALTER DATABASE RENAME FILE '/appsolwolrd/temp01.dbf' TO '/u02/temp01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 1026 - file is in use or recovery
ORA-01110: data file 1026: '/appsolworld/temp02.dbf'

 Now the  Tempfile status

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                         STATUS
---------- -------------------------         -------
 1         /appsolworld/temp01.dbf           OFFLINE
 2         /appsolworld/temp02.dbf           ONLINE
 3         /appsolworld/temp03.dbf           ONLINE

Copy the tempfile that has to move in new location.

SQL> !cp -p /appsolworld/temp01.dbf /appsolworld/temp01.dbf


Need to copy it first before rename it, if not then will get this error :

SQL> ALTER DATABASE RENAME FILE '/appsolworld/temp01.dbf' TO '/u02/temp01.dbf';
ALTER DATABASE RENAME FILE '/appsolwolrd/temp01.dbf' TO '/u02/temp01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 1025 - new file '/appsolworld/temp01.dbf' not found
ORA-01110: data file 1025: '/u01/temp01.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

 Rename Tempfile that already move to other location

SQL> ALTER DATABASE RENAME FILE '/appsolworld/temp01.dbf' TO '/appsolworld/temp01.dbf';

  The tempfile status after moved to other location

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                         STATUS
---------- -------------------------         -------
 1         /appsolworld/temp01.dbf           OFFLINE
 2         /appsolworld/temp02.dbf           ONLINE
 3         /appsolworld/temp03.dbf           ONLINE

 This ONLINE tempfile that has to move

SQL> ALTER DATABASE TEMPFILE '/appsolworld/temp01.dbf' ONLINE;

The Tempfile status after moved

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                         STATUS
---------- -------------------------         -------
 1         /appsolworld/temp01.dbf           ONLINE
 2         /appsolworld/temp02.dbf           ONLINE
 3         /appsolworld/temp03.dbf           ONLINE

 Remove the old Tempfile

SQL> !rm -rf /appsolworld/temp01.dbf
One important thing regarding temp file is 
that the size of file should not increase the size of datafiles.
Adding a Datafile to Temp Tablespace

Check the current size

set pages 1000
set lines 500
col FILE_NAME format a60
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, 
d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(c.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile c
where d.FILE_ID = c.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

please ensure you are under correct PDB as temp datafiles can be at container level and/or pluggable database level.

Add Temp Datafile to Temp Tablespace

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

or just use below , let suppose need to add 200GB of temp space then run below command 19 times.

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 20G;

set line 1000
set pages 500
select file_name,bytes/1024/1024/1024 from dba_temp_files;

Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

PRVG-11250 : The check "RPM Package Manager database" was not performed because

How to troubleshoot Long Running Concurrent Request in EBS 12.2

ORA-00257:archiver error, connect internal only until freed

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

CPU Patch Analysis

How to write to a CSV file using Oracle SQL*Plus

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"