Is it Possible to ‘DROP’ a Datafile from a Tablespace?

In fact Oracle does not provide an interface for dropping datafiles like you could drop a schema and its object in case of table, a view, a user, etc. Once you add a datafile in the tablespace then the datafile cannot be removed but in some case you need do it then you can perform some work to find closer results.

How to Deal different scenario Need to remove Datafile:

Select file_name, tablespace_name from dba_data_files where tablespace_name =’SDH_TIMS_DBF’;

FILE_NAME                                 TABLESPACE_NAME

————————————      —————

D:ORACLEORADATASADHANSDH_TIMS01.DBF   SDH_TIMS_DBF

D:ORACLEORADATASADHANSDH_TIMS02.DBF   SDH_TIMS_DBF

If the datafile you want to remove is the only datafile in that tablespace then simply drop the entire tablespace:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;‎

Note: Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup.

The DROP TABLESPACE command removes the tablespace, the datafiles, and its contents from data dictionary.  Oracle will no longer have access to ANY object that was contained in this tablespace.  The physical datafile must then be removed using an operating system command (Oracle NEVER physically removes any datafiles).

If you have more than one datafile in the tablespace and you want to keep the objects of first datafile then you must export all the objects you want to keep then Drop the tablespace.

Select owner,segment_name,segment_type from dba_segments where tablespace_name='<name ‎of tablespace>’‎

Note: Make sure you specify the tablespace name in capital letters.

OWNER SEGMENT_NAME SEGMENT_TYPE
TIMS GEN_BUYER_OPEN_BALANCE TABLE
TIMS GEN_BUYER_PROFILE TABLE
TIMS GEN_BUYER_STATEMENT TABLE
TIMS GEN_COMPANY_QUANTITY_TYPE TABLE
TIMS GEN_CONTRACT_PROFILE TABLE
TIMS GEN_CONTRACT_WH_LOCATIONS TABLE
TIMS GEN_DEPOSIT_CU TABLE
TIMS GEN_DEPOSIT_INSTALLMENT TABLE
TIMS STK_ITEM_STATEMENT TABLE PARTITION
TIMS USR_SMAN_SALESMAN_FK_I INDEX
TIMS AG_DTL_PK INDEX
TIMS AG_DTL_AGING_FK_I INDEX

Now Re-create the tablespace with the desired datafiles then import the objects into that tablespace.

If you just added the datafile and Oracle has not yet allocated any space within this datafile, then you can resize to make the datafile smaller than 5 Oracle blocks.  If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.

ALTER DATABASE DATAFILE <filename> RESIZE;

Here we are not including the OFFLINE DROP command because is not meant to allow you to remove a datafile.

ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP;

ALTER DATABASE DATAFILE <datafile name> OFFLINE;  –in archivelog mod

What the above command really means is that you are offlining the datafile with the intention of dropping the tablespace. 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. The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s