Cannot add data file - when attempting to re-add a missing tempfile to a temporary tablespace

SQL> select name from v$tempfile;

NAME
-----------------------------------
/u02/oradata/scr9/temp01.dbf

SQL> !ls //u02/oradata/scr9/temp01.dbf
/u02/oradata/scr9/temp01.dbf not found

SQL> alter tablespace TEMP
add tempfile '/u02/oradata/scr9/temp01.dbf' reuse;
  2  alter tablespace TEMP
*
ERROR at line 1:
ORA-01537: cannot add data file '/u02/oradata/scr9/temp01.dbf' - file already
part of database.

Why is this happening?

This can happen if a step has been missed during a database cloning exercise.

How to fix it?

With a temporary tablespace it is possible to drop the missing tempfile and then add a new one. You can only drop a tempfile if it is not in use - but in this case the temp file doesn't actually exist, so it can't be in use. Use the following commands:

SQL> alter tablespace <TEMP_TS_NAME>
 2   drop tempfile '<FILE_PATH_AND_NAME>';

SQL> alter tablespace <TEMP_TS_NAME>
 2   add tempfile '<FILE_PATH_AND_NAME>' size <FILE_SIZE>;

For example:

SQL> alter tablespace temp
  2  drop tempfile '/u02/oradata/scr9/temp01.dbf';

Tablespace altered.

SQL> alter tablespace TEMP 
  2  add tempfile '/u02/oradata/scr9/temp01.dbf'  size 8192m;

Tablespace altered.

Sukhwinder Singh
Apps DBA