Re: How to drop a datafile permanently?

There's no way to remove a datafile from a database, short of dropping the entire tablespace (which can only be done after you've cleared it of all contents -or used the 'including contents' clause).

The best I've ever done when wishing to reverse an inappropriate addition of a datafile is to resize it down to something insignificant -alter tablespace blah resize 16K; should probably do it.

There is a slightly more long-winded way, but you have to be pretty keen to want to do it, since it means trashing your control files (and hence, can only be done with the database closed). If you issue the alter database backup controlfile to trace command, that will generate a text file in whatever is set as your user_dump_dest... edit this file by stripping out the rubbish at the top. You should be left with a sql script which says 'create controlfile blah blah blah...' and part of the blah blah blahing there is a physical description of your database -all the redo logs listed, and all the datafiles.

You simply need to cut out the line that refers to the datafile you aren't interested in.

Then just blow away all copies of your control files, fire up server manager, and run the create controlfile script: your control files will be re-created, missing the crucial pointer.

It's all rather a long-winded way of doing something that probably isn't that important to do, however.

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


<argosy22_at_my-deja.com> wrote in message news:8ui6f0$of$1_at_nnrp1.deja.com...


> Hi everyone,

>

> I can easily create a tablespace:

>

> create tablespace  data_oflow

> datafile  '/data/JUNK805/doflow01.dbf'

> size  10M

> DEFAULT storage  (

> initial 128K

> next 128K

> pctincrease 1

> minextents 2

> maxextents 80

> )

>

> and add a datafile:

>

> alter tablespace  DATA_OFLOW

> add datafile '/data/JUNK805/doflow02.dbf'

> size  10M

>

>

> But, how can I drop this datafile by itself?

>

>

> I tried:

>

> alter DATABASE

> datafile '/data/JUNK805/doflow02.dbf'

> OFFLINE

> drop

>

>

> However, the datafile still showed up when I ran my tablespace query

> script and put the datafile into a state of RECOVERy.

>

> Select status

> from v$datafile

>

> STATUS

> ------

> RECOVER

>

>

> Is there a way to permanently drop this datafile from the database?

>

> Thanks,

>

> Argosy

>

>

> P.S. - I'm glad that I tested this first!

>

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.