Wednesday, September 28, 2011

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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

There was an error in this gadget