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?
firstname.lastname@example.org> create tablespace xyz datafile 'd:\oracle\oradata\dev815nt\xyz01.dbf' size 32M;
email@example.com> host del d:\oracle\oradata\dev815nt\xyz01.dbf
firstname.lastname@example.org> alter tablespace xyz offline;
email@example.com> drop tablespace xyz including contents;
firstname.lastname@example.org> select file_name from dba_data_files;
Just bring the tablespace offline and you should be able to drop it !
I think you could do:
alter database datafile <datafile name> offline drop;
If your database is unable to open due to this reason then,
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.
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]
Click Here to Expand Forum to Full Width