Re: How to drop a tablespace when datafile is lost

Thanks a lot for the help. This worked. I was not aware of the existence of _corrupted_rollback_segment and it seems to be an undocumented feature.

FYI, the rollback segment had to be dropped before the tablespace could be dropped. Thanks to the _corrupted_rollback_segments parameter, I got no errors dropping the rollback segment.

Thanks again for the help.

--
Ludo.

In article <81fgn9$t16$1_at_nnrp1.deja.com>,   yewpc_at_rnd.celcom.com.my wrote:
> Just follow the following step. very simple
> 1. alter database datafile 'missing file' offline drop;
> 2. Shutdown your database
> 3. add a parameter _corrupted_rollback_segments = (<rollback segment
> name that exists in the missing datafile tablespace>) in your init.ora
> 4. remove the rollback segment name from rollback_segments paramter in
> init.ora
> 5. Startup your database in open mode
> 6. drop tablespace <the tablespace> including contents;
> that is. remember to remove _corrupted_rollback_segments from you
> init.ora after that.
>
> In article <81btr4$7t4$1_at_nnrp1.deja.com>,
> lbrenta_at_my-deja.com wrote:
> > Hello,
> >
> > One of the datafiles in my database has been lost. This is not a
big
> > deal, because it was the only datafile in a tablespace that was not
in
> > use anyway. Now I want to drop the tablespace.
> >
> > Unfortunately, a rollback segment was in that tablespace (and
> datafile)
> > and it gets in the way. I can't drop it because I get the ORA-01545
> > error message when trying to do so. I can't alter it to bring it
> either
> > online or offline since the datafile is gone.
> >
> > I'm not interested in recovering the datafile, and even if I were, I
> > have no archived redo logs or backups for that particular datafile.
I
> > also don't want to lose my other tablespaces, but I can afford to
shut
> > down the instance.
> >
> > So, the question boils down to this: how can I nuke a tablespace
when
> > the datafile is gone?
> >
> > Any help will be appreciated
> >
> > --
> > Ludo.
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 09:27:36 CST