ORA-01537, ORA-00376: When Recreating a Datafile After Offline Drop

刘泽宇

参照官方文档ID:100718.1
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.