deleting datafile

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.

xyz I suggest you to read Note:111316.1 from Metalink, the Note states the plain reasons but there are further internals problems to delete a datafile from a tablespace

When you 'drop' a datafile, it is not removed from the data dictionary. The key issue is that the file is still referenced in sys.fet$ (free extent table). When a segment attempts to allocate a next extent, it may try to use the datafile that no longer exists. This will cause the invoking transaction to fail. By forcing oracle to skip over the file (as per the metalink article), you insure that this situation will never be encountered.

If you do this, make certain to document the reasons. Better yet, take the next opportunity to drop and recreate the tablespace. Of course, if it is the system tablespace, you are going to have to drop and recreate the entire database.

Pando is correct.

Extract from that NOTE

Code:

---


 
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.

If you are running in archivelog mode, you can also use:

    ALTER DATABASE DATAFILE OFFLINE;

instead of OFFLINE DROP.  Once the datafile is offline, Oracle no longer
attempts to access it, but it is still considered part of that tablespace. This
datafile is marked only as offline in the controlfile and there is no SCN
comparison done between the controlfile and the datafile during startup (This
also allows you to startup a database with a non-critical datafile missing). 
The entry for that datafile is not deleted from the controlfile to give us the
opportunity to recover that datafile.

---

The best solution would be using export on tablesapce, then drop the tablespace and then re create the tablespace and do the import on it.

Sam