Problem Description
-------------------
You have offline dropped a datafile then try to recreate the datafile (ALTER
DATABASE ADD DATAFILE) with the same name and get error message:
ORA-1537 (datafile already exists) even though you have offline dropped it.
ORA-01537: "cannot add data file '%s' - file already part of database"
Cause: During CREATE or ALTER TABLESPACE, a file being added is already part of the database.
Action: Use a different file name.
ORA-00376: "file %s cannot be read at this time"
Cause: attempting to read from a file that is not readable. Most likely
the file is offline.
Action: Check the state of the file. Bring it online
Solution Description
--------------------
Before proceeding, see Note 111316.1 How to 'DROP' a Datafile from a
Tablespace.
If a tablespace has only one datafile then dropping the tablespace will remove details of the datafile from the data dictionary.
If a tablespace has more than one datafile, then to remove the datafile, the tablespace must be dropped and recreated having exported any data that needs to be retained.
To remove details of the datafile from the Data Dictionary:
1) Export the entire tablespace contents.
2) Drop the tablespace, including contents.
3) Rebuild the tablespace with the datafiles you want it to contain.
4) Import the data back in.
Explanation
-----------
The Data Dictionary still contains information that this datafile
exists. Offline drop of a datafile does not remove it from the Data
Dictionary.
-------------------
You have offline dropped a datafile then try to recreate the datafile (ALTER
DATABASE ADD DATAFILE) with the same name and get error message:
ORA-1537 (datafile already exists) even though you have offline dropped it.
ORA-01537: "cannot add data file '%s' - file already part of database"
Cause: During CREATE or ALTER TABLESPACE, a file being added is already part of the database.
Action: Use a different file name.
ORA-00376: "file %s cannot be read at this time"
Cause: attempting to read from a file that is not readable. Most likely
the file is offline.
Action: Check the state of the file. Bring it online
Solution Description
--------------------
Before proceeding, see Note 111316.1 How to 'DROP' a Datafile from a
Tablespace.
If a tablespace has only one datafile then dropping the tablespace will remove details of the datafile from the data dictionary.
If a tablespace has more than one datafile, then to remove the datafile, the tablespace must be dropped and recreated having exported any data that needs to be retained.
To remove details of the datafile from the Data Dictionary:
1) Export the entire tablespace contents.
2) Drop the tablespace, including contents.
3) Rebuild the tablespace with the datafiles you want it to contain.
4) Import the data back in.
Explanation
-----------
The Data Dictionary still contains information that this datafile
exists. Offline drop of a datafile does not remove it from the Data
Dictionary.
