Home » RDBMS Server » Server Administration » drop a datafile
drop a datafile [message #232396] Fri, 20 April 2007 06:59 Go to next message
kishore_dba
Messages: 21
Registered: March 2007
Location: HYDERABAD
Junior Member
hi,
i created a tablespace with 4 datafiles.and i want to drop a one datafile from that tablespace and this datafile containing some data. so how can i drop the datafile that having contents.
regards,
kishore.
Re: drop a datafile [message #232403 is a reply to message #232396] Fri, 20 April 2007 07:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quoting documentation

Quote:
If a datafile is not empty, it cannot be dropped.

If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

Re: drop a datafile [message #232405 is a reply to message #232396] Fri, 20 April 2007 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before 10g, you can't drop a datafile.
With 10g and up, you can only if it is empty.

Regards
Michel


Re: drop a datafile [message #232564 is a reply to message #232405] Sat, 21 April 2007 04:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
With 10g and up, you can only if it is empty.

With 10gr2 not in 10gr1.
You are not able to drop datafile in 10.1.0.2.0(10gr1) it will work on 10.2(10gr2)

regards
Taj
Re: drop a datafile [message #232565 is a reply to message #232564] Sat, 21 April 2007 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the correction, I tend to think that any release 1 does not exist. Wink

Regards
Michel
Re: drop a datafile [message #233162 is a reply to message #232396] Wed, 25 April 2007 01:15 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi kishor,

It's right that you can't drop the non-empty datafile.
But you can tell the oracle to mark it as it was dropped, Then after, you can't use that datafile. That datafile will not be dropped physically, but oracle will consider it as it was dropped..

Use the statement,
Alter database datafile offline drop ;
Re: drop a datafile [message #233169 is a reply to message #233162] Wed, 25 April 2007 01:53 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From 9i Doc:
Quote:
If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile offline. However, the DROP clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.
If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword.

So this does not drop the datafile.

In 10g this clause is replace to a more understandable FOR DROP clause with the same effect.

Regards
Michel

Previous Topic: Data Scrambling
Next Topic: oracle 10.2.0 on RHEL ES4 x64
Goto Forum:
  


Current Time: Sat Dec 10 13:05:23 CST 2016

Total time taken to generate the page: 0.07944 seconds