| drop a datafile [message #232396] |
Fri, 20 April 2007 06:59  |
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 #233162 is a reply to message #232396] |
Wed, 25 April 2007 01:15  |
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 |
 |
Michel Cadot
Messages: 68686
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
