Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 26 August 2012

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:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF   SDH_TIMS_DBF
D:\ORACLE\ORADATA\SADHAN\SDH_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.

0 comments:

Post a Comment