OraFAQ Forum: Server Administration » drop a single datafile

Home » RDBMS Server » Server Administration » drop a single datafile (Oracle 10.2.0.3 Linux AS4)

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

drop a single datafile [message #398956]

Mon, 20 April 2009 00:27

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

Report message to a moderator

Re: drop a single datafile [message #398957 is a reply to message #398956]

Mon, 20 April 2009 00:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

minimize its size & ignore it

Report message to a moderator

Re: drop a single datafile [message #398958 is a reply to message #398957]

Mon, 20 April 2009 00:33

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.

Report message to a moderator

Re: drop a single datafile [message #398959 is a reply to message #398956]

Mon, 20 April 2009 00:34

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: drop a single datafile [message #398973 is a reply to message #398959]

Mon, 20 April 2009 01:36

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

Report message to a moderator

Re: drop a single datafile [message #398974 is a reply to message #398956]

Mon, 20 April 2009 01:42

BlackSwan
Messages: 26766
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.?

Report message to a moderator

Re: drop a single datafile [message #398977 is a reply to message #398974]

Mon, 20 April 2009 02:01

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

Report message to a moderator

Re: drop a single datafile [message #398980 is a reply to message #398977]

Mon, 20 April 2009 02:08

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: drop a single datafile [message #399042 is a reply to message #398974]

Mon, 20 April 2009 06:16

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.

Report message to a moderator

Previous Topic:	ORA-27092: skgfofi: size of file exceeds file size
Next Topic:	database failure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 00:20:01 CDT 2024