Re: How to drop a tablespace when datafile is lost

James,

Thanks for the help. The ALTER DATABASE DATAFILE 'filename' OFFLINE DROP worked, but I am unable to remove the tablespace because of the stale rollback segment. If I try to bring the tablespace offline, I get ORA-01191 (already offline). If I try to bring the rollback segment offline, I get ORA-01598 (not online). If I try to drop the tablespace including contents, I get ORA-01548 (active rollback segment found). If I try to drop the rollback segment, I get ORA-01545 (specified rollback segment is not available).

Perhaps the best would be to recreate my control files? Comments anyone?

Thanks

--
Ludo.

In article <81c58g$dre$1_at_nnrp1.deja.com>,   James Lorenzen <james_lorenzen_at_allianzlife.com> wrote:
> It's been awhile, but I believe that you can clear this up by going
into
> svrmgr:
> 1. STARTUP MOUNT
> 2. ALTER DATABASE DATAFILE 'filename' OFFLINE DROP ;
> — This will remove the missing datafile from the database.
> 3. ALTER TABLESPACE tablespace_name OFFLINE ;
> — This should take the tablespace offline.
> 4. ALTER DATABASE OPEN ;
> 5. DROP TABLESPACE tablespace_name ;
>
> I don't know what the presence of a rollback segment on the missing
file
> would do this process.
>
> HTH
> James
> 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 Tue Nov 23 1999 - 11:37:17 CST