OraFAQ Forum: Backup & Recovery

Home » RDBMS Server » Backup & Recovery » Drop datafile

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

Drop datafile [message #218886]

Sun, 11 February 2007 22:35

sanjay_kant
Messages: 25
Registered: February 2007

Junior Member

Can we drop datafiles... if so... plz tell me how?

Report message to a moderator

Re: Drop datafile [message #218888 is a reply to message #218886]

Sun, 11 February 2007 22:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can we drop datafiles... if so... plz tell me how?
Yes, but it is usually a VERY BAD idea.

Report message to a moderator

Re: Drop datafile [message #218892 is a reply to message #218888]

Sun, 11 February 2007 23:05

sanjay_kant
Messages: 25
Registered: February 2007

Junior Member

i think we can drop it by

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

it will work. but when u'll check status in v$datafile it will show RECOVER there. means datafile is still present in data dictionary.

then the question is how to remove in from there???

plz tell me

Report message to a moderator

Re: Drop datafile [message #218903 is a reply to message #218892]

Sun, 11 February 2007 23:37

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

HI,

How to drop datafile.
if oracle 10gr1 > 10.1.x.x.x
there is not sql command to drop datafile directly.
you have to create new tablespace and move all data from old to new tablespace and drop old tablespace.
if oracle 10gr2 > 10.2.x.x.x
then you can use below command

alter tablespace tablespace_name DROP DATAFILE 'path\filename';

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

above command not drop datafile it is just mark "drop" means you cann't use above datafile after open database.

regards
Taj

Report message to a moderator

Re: Drop datafile [message #218968 is a reply to message #218886]

Mon, 12 February 2007 05:03

ebrian
Messages: 2794
Registered: April 2006

Senior Member

There are restrictions to using the DROP DATAFILE in 10gR2, see Restrictions for Dropping Datafiles.

Report message to a moderator

Previous Topic:	Diff Between Clone and standby Database
Next Topic:	Comparing Oracle 9i with 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 17 16:08:31 CDT 2025