Re: Rollback Segment Won't Drop

My guess is that bouncing the database killed any current transactions. But on database startup, those killed transactions would still need to be rolled back. Therefore, the data in the rollback segment is still needed. Once the instance recovery is fully complete, then the data in the rollback segment is not needed and the rollback segment can be removed. Have you checked this today?

HTH,
Brian

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
Received on Tue Oct 22 2002 - 10:48:35 CDT