| dropping a datafile from the tablespace. [message #193926] |
Tue, 19 September 2006 23:58  |
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  |
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 #194022 is a reply to message #193930] |
Wed, 20 September 2006 08:14 |
joy_division
Messages: 4963
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 |
 |
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>'; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
