Re: Dropping a datafile from a tablespace

"Sam Kome" <skome_at_unc.edu> writes:

>As an Oracle amateur, I am seeking help with the following problem:
 

> I used Alter Tablespace add datafile to add a file to my user_data
>tablespace to the tune of about 90 Mb. The usefulness of this datafile has
>expired, so I reached for the Alter Tablespace drop datafile command...
>which doesn't exist. After a thorough search of TFM, I don't see a polite
>way to reclaim the space.
 

>TIA,
 
>Sam Kome

Sam,

You are on the right track. The command to drop a datafile is:

ALTER DATABASE DATAFILE '/path/file_name' OFFLINE DROP;

Be sure to make an export before issuing this command, if you are concerned about losing data. Otherwise, you can export the data, drop the tablespace, recreate the tablespace with smaller datafiles, and import the data.

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->