Re: How to drop a tablespace when datafile is lost

Hi:

Just a late thought, Is the Rollback segment name is included in the ROLLBACK_SEGMENTS parameter in the init<oracle_sid>.ora file ? Try Removing it and repeat the previous steps. Also I feel that this is a bit shady.... because I think that when at database startup Oracle should perform Crash recovery ( if the database was shutdown abort or "killed" abruptly ) So the uncommitted data has to be rollforwarded and then rollbacked. So the best solution could be recover the tablespace ( provided U have a valid backup and the db is running in archive log mode :( ) . HTH,
Vinayak

In article <81ejcc$6oe$1_at_nnrp1.deja.com>,   lbrenta_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 23 1999 - 12:41:55 CST