Re: How do you DROP an empty Oracle datafile. ???

alter tablespace XXX offline;
drop tablespace XXX including contents;

The actual datafile you remove through the OS, not from Oracle. If you use NT, you need to wait awhile before you can actually remove the file. NT keeps the file in use for sometime.

--
Terry Dykstra
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

<srana5190_at_my-deja.com> wrote in message news:88jjs5$vjm$1_at_nnrp1.deja.com...
> Any one can explain me the step how to get rid of tablespace. I have
> kind of simillar problem I have three tablespace and all of them has
> one datafile. I tried to rename the datafile because those datafile
> don't have any extenstion it always give me the error the file is not
> same operating system. Any way I wanted to get rid of those tablespaces
> and offcourse the datafile belonging to them. so please could any one
> explain the step for droping the tablespace my database is runing
> noarchivelog mode. Do I need to recreate the control file.
>
> Thanks in addvance
>
>
>
> In article <38A97D10.FD9AD3A8_at_abbnm.com>,
> Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote:
> > Kevin,
> > Hi. I have read all your subsequent posts also on this thread. What I
> would do
> > is use a reverse engineering tool to get the ddl for all the indexes
> and then
> > drop the tablespace and then recreate the indexes. I would use either
> Toad or
> > any of the Embarcadero Tools .
> >
> > Tapan
> >
> > Kevin A Lewis wrote:
> >
> > > I have a database which has an empty datafile. This has been
> created in the
> > > wrong place to extend an tablespace for index objects. It was
> noticed that
> > > this was the wrong place and then OFFLINE DROPed. I now find no
> easy way to
> > > get rid of the datafile and its reference in the controlfile. It
> remains in
> > > an OFFLINE and RECOVER state.
> > >
> > > The database is running in noarchivlog mode and I have tried
> creating a new
> > > controlfile. To my surprise the file would not disappear even
> though the
> > > script derived from 'BACKUP CONTROFILE TO TRACE' had the reference
> to the
> > > offending file removed.
> > >
> > > I am running on Oracle 8.0.5 on Compaq Tru64 Unix 4F
> > >
> > > Thanks
> > >
> > > --
> > > Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich
> United
> > > Kingdom)
> > > <Kevin_A_Lewis_at_Hotmail.com>
> > >
> > > The views expressed herein by the author of this document
> > > are not necessarily those of BOCM PAULS Ltd.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Feb 18 2000 - 09:44:22 CST