Hi, Welcome to new year of challenges,
Can we delete Datafile without dropping the tablespace?
Thanks in Adv.
Printable View
Hi, Welcome to new year of challenges,
Can we delete Datafile without dropping the tablespace?
Thanks in Adv.
No. You have to empty the tablespace, drop the tablespace, and then recreate the tablespace.
Yes. You can drop datafile without dropping tablespace.
alter database datafile '' offline drop;
database has to be in mount mode.
XYZ is right... A datafile can be dropped without dropping tablespace.
Sanjay
Yes is correct you can drop a datafile without drop the tablespace, but you must verify that the datafile is empty.
Regards
Angel
you cannot drop a datafile from a tablespace unless you are gonna drop the entire tablespace, if you do offline drop that really is
1. I am preparing to drop the file
2. After drop this file I will proceed to drop the tablespace
Add a datafile is easy, drop one is not possible without dropping the tablespace.
Btw offline drop is normally used in database in noarchive log where you have lost a datafile and you want to start the database without getting error (otherwise you will get an error message complaining about missing datafile)
refer to back & recovery doco if you have further doubts
So, What's the final conclusion for the database running in Archive log mode?
Pl. clear...
Thanks to all
This is the problem with free advice, it's worth what you pay. I suggest you try it in your development environment to see if you get the expected result (which I am sure you won't).
well even in archive log mode you cannot delete a datafile from a tablespace, this is not dependant on archive or noarchive just that in archive log mode you can do alter database datafile xxxx offline but it´s not possible in noarchive, you are forced to do offline drop, in either case you have to get rid of whole tablespace anywayz
You can delete a datafile from tablespace without delete tablespace. After run
alter database datafile 'xxx' offline drop;
You only delete the datafile from your control file, you can use os to delete datafile and later you still can open the database. Keep in mind that datafile name is still in dictionary view. You can't use add datafile to add this datafile anymore. But you can make it online again (if you did not delete datafile in OS). By the way, database objects (such as tables, indexes) will be in any of theses datafiles in this tablespace, if you have any data in this tablespace which have more than one datafiles, even you only dedete one datafile, you may lost your data. If you can lost any data in this tablesace, then you should drop your tablesapce and recreate it later. Then database control file and database dictionary view will give you same information about data files.