ORA-01537: cannot add data file
What are the symptoms?
An ORA-01537 is thrown 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. For advice and examples of how to clone a database, check out the cloning guides here.
How do I 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:
alter tablespace <TEMP_TS_NAME>
drop tempfile '<FILE_PATH_AND_NAME>';
alter tablespace <TEMP_TS_NAME>
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.
For more useful DBA queries click here.
| 