Home » RDBMS Server » Server Administration » dropping a datafile from the tablespace.
dropping a datafile from the tablespace. [message #193926] Tue, 19 September 2006 23:58 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I had a tablespace "USER_DATA" and all user objects were in the same tablespace. The size of this tablespace was around 50GB.

So I created another Tablesapce "USER_DATA_LARGE" and moved all large object (having size more than 100MB) to this tablespace.

So the used space size in the "USER_DATA" reduced to 1%. Now I want to drop some datafiles belongs to "USER_DATA". How to delete the datafile (there could be some objects stored in this datafile->tablespace).

Is there a better way to drop datafile without harming the objects (I can create a temporary tablespace and move all objects to the temp-tablespace, drop the original, create original tablespace and move back the objects)

Thanking you,
Brayan.
Re: dropping a datafile from the tablespace. [message #193930 is a reply to message #193926] Wed, 20 September 2006 00:26 Go to previous messageGo to next message
Shamsher
Messages: 18
Registered: September 2006
Junior Member
You can follow this approch. Take Backup of all datafile.

Then find File id's of datafile of your desired tablespace.

select substr(name,1,50),file# from v$datafile ;

Then find if any objects are there in that datafile or not using below query on dba_extent view.
select segment_name,tablespace_name,segment_type,file_id from dba_extents where file_id=9 ;

Note File_id is same as File# of v$datafile which you want to drop.

In case you find any object that following query you can move that object to another tablespace.

If its table then use

Alter table abc move tablespace user_large_tablespace ;
if index better to recreat that index.

after moving all the objects you can use for all the datafile you want to drop in tablespace.

alter database datafile <DATAFILE> offline drop ;

and take a new backup of controlfile ;


Regards
Shamsheer




Re: dropping a datafile from the tablespace. [message #193994 is a reply to message #193926] Wed, 20 September 2006 05:55 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
You should be able to resize the datafiles a lot.
Set autoextend to NO - You have plenty of space Wink

You might see that You cannot resize specific files below a certain point, if there are objects there.
Once You have resized the files, You could possible rebuild/move them again, hoping that they now are moved to the beginning of a file, so that You can resize further....

Or You could simply move them to another tablespace, resize/drop and possible move them back.

If You drop files, You must table a backup just after, as You are making structural changes to the database.

Br
Kim Anthonisen
Re: dropping a datafile from the tablespace. [message #194022 is a reply to message #193930] Wed, 20 September 2006 08:14 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Shamsher wrote on Wed, 20 September 2006 01:26


alter database datafile <DATAFILE> offline drop ;



Unless something changed in 10g (I don't know ALL of the features 10g added), you cannot drop a datafile from a tablespace. You can only drop the entire tablespace or like Kim said, just shrink them down to as small as they can get.
Re: dropping a datafile from the tablespace. [message #194032 is a reply to message #194022] Wed, 20 September 2006 08:46 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Joy is correct. Dropping a datafile isn't available until 10g. 10g does allow you to drop a datafile IF it is empty (ie. no extents allocated to it) and it isn't the last file in the tablespace and it isn't in a READ ONLY tablespace.

ALTER TABLESPACE <tblspace_Name> DROP DATAFILE '<datafile_name>';
Previous Topic: recovery after operating system crash
Next Topic: LOcation of Pfile
Goto Forum:
  


Current Time: Thu Dec 08 04:21:47 CST 2016

Total time taken to generate the page: 0.16179 seconds