How to drop tablespace with missing datafile?

Ok, ok ... I can hear you laughing already.

I inadvertently deleted a datafile from a tablespace that I no longer need. Now I'd like to drop the tablespace it was associated with.

Can I do it? And how?

[email protected]> create tablespace xyz datafile 'd:\oracle\oradata\dev815nt\xyz01.dbf' size 32M;

Tablespace created.

[email protected]> host del d:\oracle\oradata\dev815nt\xyz01.dbf

[email protected]> alter tablespace xyz offline;

Tablespace altered.

[email protected]> drop tablespace xyz including contents;

Tablespace dropped.

[email protected]> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------
D:\ORACLE\ORADATA\DEV815NT\SYSTEM01.DBF
D:\ORACLE\ORADATA\DEV815NT\RBS01.DBF
D:\ORACLE\ORADATA\DEV815NT\USERS01.DBF
D:\ORACLE\ORADATA\DEV815NT\TEMP01.DBF
D:\ORACLE\ORADATA\DEV815NT\TEST_DATA01.DBF

[email protected]>

Just bring the tablespace offline and you should be able to drop it !

- Rajeev

I think you could do:

alter database datafile offline drop;



If your database is unable to open due to this reason then,

shutdown immediate
startup mount
alter database datafile '/app/oracle/oradata/CM/CMetable01.dbf' offline drop;
alter database open;



Hope this helps!

Sorry I don't agree with you as for as general procedure to drop datafile is concerned with:

Don't do/make a habbit (no matter tablespae has data/not) what rpardip suggested for god sake if you are in archive log mode. You might be at work whole night if you have data in the datafile. You can't bring back your tablespace online. it asks for recovery. Best is export contents of tablespace and then offline and drop, and rebuild the one you dropped.

DROP TABLESPACE xyz OFFLINE INCLUDING CONTENTS ;

General reminder, what we tend to do in hurry in day-to-day life. A big lesson I learned from it being at work 36 hours, unfortunately I didn't have good backup either.


***ORACLE NOTE:***
The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

[Edited by sreddy on 01-23-2001 at 07:30 PM]