Re: Rollback Segment Won't Drop

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 Received on Mon Oct 21 2002 - 13:26:15 CDT