Home » RDBMS Server » Server Administration » drop a single datafile (Oracle 10.2.0.3 Linux AS4)
drop a single datafile [message #398956] Mon, 20 April 2009 00:27 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi ,

How do i drop a single datafile of the tablespace which has 4 datafiles in that tablespace?

Actually I just added a datafile in a tablespace and by mistake I did specify the wrong location for the datafile . So I want to either move or drop the newly added datafile and specify in the correct location .

Suggest me please what to do .

Raj
Re: drop a single datafile [message #398957 is a reply to message #398956] Mon, 20 April 2009 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
minimize its size & ignore it
Re: drop a single datafile [message #398958 is a reply to message #398957] Mon, 20 April 2009 00:33 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
No , I Can't do that . My seniors want me to remove and specify the corerect location for the datafile. In 10g , there is an option but I'm not sure of that.
Re: drop a single datafile [message #398959 is a reply to message #398956] Mon, 20 April 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So I want to either move

Do it, see ALTER DATABASE RENAME FILE statement.
Carefully read the documentation.

Regards
Michel
Re: drop a single datafile [message #398973 is a reply to message #398959] Mon, 20 April 2009 01:36 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Hi,

If you are using oracle 10g with release 2 ,you can drop a datafile within a tablespace


try this

for example.


alter tablespace ts_name drop datafile '/path/to/datafile.dbf'





whereas in earlier version the only means of dropping a datafile is to drop the tablespace that contains the datafile.

DROP TABLESPACE users INCLUDING CONTENTS;


Regards,
Balaji
Re: drop a single datafile [message #398974 is a reply to message #398956] Mon, 20 April 2009 01:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>DROP TABLESPACE users INCLUDING CONTENTS;
& what happens to data integrity after such a DROP?
PK/FK/Check constraints,etc.?
Re: drop a single datafile [message #398977 is a reply to message #398974] Mon, 20 April 2009 02:01 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


go through this link, you will get answers for your question.


http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9004.htm#SQLRF01807
Re: drop a single datafile [message #398980 is a reply to message #398977] Mon, 20 April 2009 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question is NOT to drop the tablespace but to move/rename the datafile.
ONLY if it would be not possible OP asks to drop it.
Hopefully you are not forced to drop the tablespace if you add a file in the wrong place.

Regards
Michel
Re: drop a single datafile [message #399042 is a reply to message #398974] Mon, 20 April 2009 06:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, @BlackSwan, nothing will happen to the data as a result of dropping a datafile, as one of the requirements of doing ALTER TABLESPACE ... DROP DATAFILE is that the datafile is empty.

ALTER TABLESPACE RENAME DATAFILE is definitely the better solution though.
Previous Topic: ORA-27092: skgfofi: size of file exceeds file size
Next Topic: database failure
Goto Forum:
  


Current Time: Sun Dec 04 06:31:10 CST 2016

Total time taken to generate the page: 0.09983 seconds