Home » RDBMS Server » Server Administration » Drop datafile from tablespace
Drop datafile from tablespace [message #194790] Mon, 25 September 2006 05:58 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I've added datafile by mistake to a TABLESPACE using

alter database datafile '/production/oradata/data/data_med02.dbf' offline drop ;

Now, when I query in dba_data_files, still the datafile is present.

select * from dba_data_files where tablespace_name = 'TABLESPACE_BIG'

Can I drop the datafile permanently from the TABLESPACE.

I mean, need to remove the entry from the dba_data_files.

Regards,
Brayan.
Re: Drop datafile from tablespace [message #194986 is a reply to message #194790] Tue, 26 September 2006 07:42 Go to previous messageGo to next message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
That is rather what we can't do. Once the datafile is added it can't be removed from the list. But yes you can resize it to the least possible value, this way atleast you can restore the space allocated to that datafile.

Thanks!
Re: Drop datafile from tablespace [message #194990 is a reply to message #194986] Tue, 26 September 2006 08:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10gR2, you can 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>';

[Updated on: Tue, 26 September 2006 08:41]

Report message to a moderator

Re: Drop datafile from tablespace [message #194998 is a reply to message #194990] Tue, 26 September 2006 08:13 Go to previous messageGo to next message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
Very true!!

But Ebrain.. I think this option is valid only from 10gR2 isn't??
Re: Drop datafile from tablespace [message #195006 is a reply to message #194998] Tue, 26 September 2006 08:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That is correct. I changed the post above to prevent any confusion.
icon7.gif  Re: Drop datafile from tablespace [message #195007 is a reply to message #195006] Tue, 26 September 2006 08:43 Go to previous message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
Thank you so much Ebrain for confirming.. Got confused for a while.
Previous Topic: db_files and maxdatafiles
Next Topic: SERVICE_NAMES in pfile
Goto Forum:
  


Current Time: Wed Dec 07 10:54:17 CST 2016

Total time taken to generate the page: 0.13040 seconds