Home » SQL & PL/SQL » SQL & PL/SQL » Dropping a Datafile and Deleting its References
Dropping a Datafile and Deleting its References [message #241392] Tue, 29 May 2007 07:57 Go to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Using sqlplus how do I delete datafiles and any references to them?

At the moment I start the database in mount mode and use:
ALTER DATABASE DATAFILE 'O:\BCS\test.ora' OFFLINE DROP;
Which drops the datafile, but when I try to open the database it fails because it is still referencing the datafile.

How do I delete this reference?

Any thoughts or suggestions are greatly appreciated.

Regards
Toby
Re: Dropping a Datafile and Deleting its References [message #241422 is a reply to message #241392] Tue, 29 May 2007 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DROP doesn't drop the datafile and does not remove the references to it.
You should never use this.
It is a bad statement.
There is no way to remove a file from a tablespace unless you are in the latest version.

Post your Oracle version.

Regards
Michel
Re: Dropping a Datafile and Deleting its References [message #241424 is a reply to message #241392] Tue, 29 May 2007 09:34 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Thanks for the reply.

My Oracle version is 9i

So what does the offline drop command actually do?
Re: Dropping a Datafile and Deleting its References [message #241433 is a reply to message #241424] Tue, 29 May 2007 09:44 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It just indicates to Oracle that you offline the file and don't want to online it any more.
In 10g, this was renamed to a better "offline FOR drop".

Regards
Michel
Previous Topic: Date Range Partitions
Next Topic: How to design table for this particular requirement
Goto Forum:
  


Current Time: Sat Dec 03 12:10:27 CST 2016

Total time taken to generate the page: 0.08425 seconds