Rollback Segment Won't Drop

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 Mon Oct 21 2002 - 12:18:24 CDT