How to drop tablespace with missing datafile?

[tjn]

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?

[marist89]

system@dev815nt.us> create tablespace xyz datafile 'd:\oracle\oradata\dev815nt\xyz01.dbf' size 32M;

Tablespace created.

system@dev815nt.us> host del d:\oracle\oradata\dev815nt\xyz01.dbf

system@dev815nt.us> alter tablespace xyz offline;

Tablespace altered.

system@dev815nt.us> drop tablespace xyz including contents;

Tablespace dropped.

system@dev815nt.us> 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

system@dev815nt.us>

[rsuri]

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

- Rajeev

[rpardip]

I think you could do:

alter database datafile <datafile name> 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!

[sreddy]

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 <datafile name> 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]