Home » RDBMS Server » Backup & Recovery » Drop datafile
icon5.gif  Drop datafile [message #218886] Sun, 11 February 2007 22:35 Go to next message
sanjay_kant
Messages: 25
Registered: February 2007
Junior Member

Can we drop datafiles... if so... plz tell me how?
Re: Drop datafile [message #218888 is a reply to message #218886] Sun, 11 February 2007 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25043
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.
icon7.gif  Re: Drop datafile [message #218892 is a reply to message #218888] Sun, 11 February 2007 23:05 Go to previous messageGo to next message
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
Re: Drop datafile [message #218903 is a reply to message #218892] Sun, 11 February 2007 23:37 Go to previous messageGo to next message
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
Re: Drop datafile [message #218968 is a reply to message #218886] Mon, 12 February 2007 05:03 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There are restrictions to using the DROP DATAFILE in 10gR2, see Restrictions for Dropping Datafiles.
Previous Topic: Diff Between Clone and standby Database
Next Topic: Comparing Oracle 9i with 10g
Goto Forum:
  


Current Time: Wed Dec 07 08:34:40 CST 2016

Total time taken to generate the page: 0.09166 seconds