CODEWITHSUNDEEP
oracle-databaseoracle10g
magqq
magqq

Reputation: 1023

Oracle Database: May I drop a datafile created by mistake?

I need to get rid off a data file which created mistakenly, the db is Oracle 10g R2. May I do the following?

alter tablespace abc drop datafile '/abc.dbf'

The data file abc.dbf is in offline (needs recovery) state and 0 byte. Since it is offlined, I think it can't be dropped this way, so what's your suggestions?

Upvotes: 0

Views: 2817

Answers (1)

Ilya Saunkin
Ilya Saunkin

Reputation: 19820

I would try to bring it back online first, then drop.

alter database abc datafile '/abc.dbf' online;

you'll get an ORA error saying that the file needs recovery, do that

recover datafile '/abc.dbf';

bring it online and drop it then

alter tablespace abc drop datafile '/abc.dbf';

If that doesn't work for you, do either of:

  • export tablespace objects of interest, drop the whole tablespace, create it again and import the exported file
  • create a new tablespace, copy objects, recreate indexes and drop the old tablespace

Note: if you go with dropping the tablespace, don't forget to also specify to drop the datafiles with it.

drop tablespace abc including contents and datafiles;

Upvotes: 1

Related Questions