Re: 8i Tablespace removal if someone has deleted the .dbf instead of dropping the tablespace

In our last gripping episode "Niall Litchfield" <n-litchfield_at_auditcommission. gov.uk> wrote:
> From memory so check the syntax.
>
> alter tablespace X offline drop;
>
> regards
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> <eric_dewerth_at_my-deja.com> wrote in message
> news:918lv0$6ud$1_at_nnrp1.deja.com...
> > I have an 8i dB with several Tablespaces the were deleted by rm
> > the .dbf instead of removing the tablespace. It is causing the
> > Instance not to OPEN...It will MOUNT but not OPEN. How do I get
 Oracle
> > to recognize that the tablespace is gone? How do I remove the
> > tablespace since there is no .dbf associated with it?
> >
> > Thanks,
> > Eric
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Mount the database, then issue the following:

drop tablespace ... including contents;

This should drop the "offending" tablespace and allow the instance to open.

ALTER TABLESPACE can take a tablespace offline but there is no DROP option. One can take a datafile offline and drop it with ALTER DATABASE DATAFILE ... OFFLINE DROP.

--
David Fitzjarrell
Oracle Certified DBA


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