OraFAQ Forum: Server Administration » Drop datafile from tablespace

Home » RDBMS Server » Server Administration » Drop datafile from tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Drop datafile from tablespace [message #194790]

Mon, 25 September 2006 05:58

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.

Report message to a moderator

Re: Drop datafile from tablespace [message #194986 is a reply to message #194790]

Tue, 26 September 2006 07:42

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!

Report message to a moderator

Re: Drop datafile from tablespace [message #194990 is a reply to message #194986]

Tue, 26 September 2006 08:01

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

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??

Report message to a moderator

Re: Drop datafile from tablespace [message #195006 is a reply to message #194998]

Tue, 26 September 2006 08:41

ebrian
Messages: 2794
Registered: April 2006

Senior Member

That is correct. I changed the post above to prevent any confusion.

Report message to a moderator

Re: Drop datafile from tablespace [message #195007 is a reply to message #195006]

Tue, 26 September 2006 08:43

ramakris
Messages: 21
Registered: July 2006
Location: Delhi

Junior Member

Thank you so much Ebrain for confirming.. Got confused for a while.

Report message to a moderator

Previous Topic:	db_files and maxdatafiles
Next Topic:	SERVICE_NAMES in pfile

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 08:24:08 CDT 2024