Oracle – DB Temp flle has been deleted!

Actions to take when the temp file has been accidentally deleted

If the temp file is removed / deleted when the database is shut down then the DB is clever enough to realize that its missing and should create a new file when it’s brought back online.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/temp01.dbf

[oracle@localhost orcl]$ ls

afiedt.buf                                       APEX_2614203650434107.dbf     redo03.log

APEX_1930613455248703.dbf     control01.ctl                                  sysaux01.dbf

APEX_2041602962184952.dbf     example01.dbf                               system01.dbf

APEX_2610402357158758.dbf     redo01.log                                     undotbs01.dbf

APEX_2611417663389985.dbf     redo02.log                                     users01.dbf

SQL> startup

[oracle@localhost orcl]$ ls

afiedt.buf                                        control01.ctl                   system01.dbf

APEX_1930613455248703.dbf      example01.dbf                temp01.dbf

APEX_2041602962184952.dbf      redo01.log                      undotbs01.dbf

APEX_2610402357158758.dbf      redo02.log                      users01.dbf

APEX_2611417663389985.dbf      redo03.log

APEX_2614203650434107.dbf      sysaux01.dbf

* Temp01 file has been magically re-created

If the database is up when the file is removed then you can manually re-create it on the fly as follows:

-- create a new temp file

ALTER TABLESPACE TEMP ADD TEMPFILE

'/home/oracle/app/oracle/oradata/orcl/temp02.dbf' SIZE 50M;

-- Drop the old file

ALTER TABLESPACE TEMP DROP TEMPFILE

'/home/oracle/app/oracle/oradata/orcl/temp01.dbf';

SQL> select file_name, tablespace_name from dba_temp_files;

FILE_NAME                                                                 TABLESPACE_NAME

/home/oracle/app/oracle/oradata/orcl/temp02.dbf       TEMP

Or if you are able to restart the database, then you can let the database re-create it for you.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/temp02.dbf

SQL> shutdown immediate

SQL> startup

[oracle@localhost orcl]$ ls

afiedt.buf                                       control01.ctl                    system01.dbf

APEX_1930613455248703.dbf     example01.dbf                 temp02.dbf

APEX_2041602962184952.dbf     redo01.log                       undotbs01.dbf

APEX_2610402357158758.dbf     redo02.log                       users01.dbf

APEX_2611417663389985.dbf     redo03.log

APEX_2614203650434107.dbf     sysaux01.dbf