Re: Rollback Segment Won't Drop

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DB446B7.CC3523BF_at_exesolutions.com>...
> Paul Cowan wrote:
>
> > I rarely write to groups for help, but I'm really stumped on this one!
> >
> > All I am trying to do is drop a (poorly created) rollback segment.
> >
> > First I altered the segment to set offline, but it went to the
> > "PENDING OFFLINE" status. You can't drop a PENDING OFFLINE segment,
> > which means there are transactions in progress on the segment. So I
> > ran this query;
> >
> > SELECT name, xacts "Active" FROM v$rollname, v$rollstat WHERE status =
> > 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;
> >
> > Which shows 1 active transaction on RBS7.
> >
> > Then I ran this query, which should show me the transactions on RBS7.
> >
> > SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK" FROM V$SESSION
> > S, V$TRANSACTION T, V$ROLLNAME R WHERE R.NAME = 'RBS7' AND S.TADDR =
> > T.ADDR AND T.XIDUSN = R.USN;
> >
> > But there are NO results. Empty.
> >
> > Next I restarted Oracle with the segment set offline. When it came
> > back up, the DBA_ROLLBACK_SEGS view shows the status is now "PARTLY
> > AVAILABLE". So Oracle STILL thinks there's a transaction to be
> > resolved?
> >
> > I tried dropping it again, but it won't drop in the "PARTLY AVAILABLE"
> > status. So I tried to alter it and set offline... won't go offline.
> >
> > Next I tried to set it online then offline. Well, it went back online
> > fine, but won't go offline. Now it's back in the "PENDING OFFLINE"
> > status again. Still, the query above shows no transactions for the
> > segment.
> >
> > Obviously, Oracle thinks there is a transaction going on, but after
> > completely restarting Oracle I thought it would go away. I can't
> > figure out what the transaction is. I've cleared out all users on the
> > db, so I know it's not a user process holding the transaction.
> >
> > Any ideas? Is there a way to figure out what is causing the segement
> > to think there's a transaction in progress, or see what the
> > transaction is? Is there such a thing as a "ghost" transaction?
> >
> > Thanks
> > Paul
>
> I can't tell you what or why. But my inclination would be to bounce the
> database.
>
> Daniel Morgan

How does that differ from completely restarting Oracle?

Yes, Oracle thinks there is a transaction. It sounds like you will have to
do some recovery, but golly gosh, how to figure out what is wrong?

I'd say, follow the instructions in note 28812.1 on metalink, which is basically setting a trace and bouncing the db to find out which object needs to be dropped to let the rb seg be freed.

jg

--
@home is bogus.

Could http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&newwindow=1&th=ccd9778131e9065d&rnum=39
be the first thread Daniel and I both posted to?