Re: 2 questions, deleting a tablespace on a damaged instance and tablespace

"Stan Brown" <stanb_at_panix.com> wrote in message news:bl9a5b$ks4$1_at_reader2.panix.com...
> In <bl99bk$kbg$1_at_reader2.panix.com> Stan Brown <stanb_at_panix.com> writes:
>
> >In <duUdb.129017$bo1.58527_at_news-server.bigpond.net.au> "Richard Foote"
 <richard.foote_at_bigpond.com> writes:
>
>
> >>"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> >>news:3f774ec9$0$32058$afc38c87_at_news.optusnet.com.au...
> >>> Stan Brown wrote:
> >>>
 

> >OK, thnaks for the clarification. I wasn't having any luck with the
> >original syntax.
 

> >So, for the file I have already deleted physicaly (which maps one to one
 to
> >a tablespace), then I should use something like "drop tablespcae
> >'table_space_name'"? ? After doing a startup mount?
 

> >Or would it be an alter databse command?
 

> >The exact syntax would be _most_ helpful. The instnaces is 7.3.4.5
>
>
> OK, I definatley need some syntax help here. I've tried the following:
>
> SVRMGR> drop tablespace INDEX1_TS ;
> drop tablespace INDEX1_TS

Hi Stan,

You can't drop a tablespace unless the database is open. As Howard mentioned previously, you need to take the stuffed datafile offline first whilst the database is in a mounted state. However, IIRC with Oracle7, you get an error if you include the DROP clause if the database is in archivelog mode (the drop clause is simply ignored in later releases). So you need to issue:

ALTER DATABASE DATAFILE 7 OFFLINE; You should now be able to open the database (assuming nothing else is wrong).

ALTER DATABASE OPEN; You can now drop this damned tablespace of yours.

DROP TABLESPACE index1_ts INCLUDING CONTENTS CASCADE CONTRAINTS;

Good Luck

Richard Received on Mon Sep 29 2003 - 08:15:33 CDT