Re: Active rollback with no sessions

Kimon Andreou <kandreou_at_my-deja.com> wrote in message news:855q1u$ev6$1_at_nnrp1.deja.com...
> i am trying to drop a tablespace and but i am unable to since it
> contains an
> active rollback segment. The problem is that there are no active
> sessions or
> active transactions currently running, but oracle tells me there is one
> as
> noted below:
>
> NAME XACTS STATUS
> ------------------------------------
> system 0 online
> rollbig 1 Pending offline
> r01 0 online
> r02 0 online
> r03 0 online
>
> when trying to drop the tablespace gives:
> ora-01546: tablespace contains active rollback segment 'rollbig'
>
> trying to take rollback segment offline gives:
> ora-01545: rollback segment 'rollbig' specified not available.
>
> i need to be able to take rollbig offline to drop the tablespace and
> re-create it to have a functional big rollback segment.
>
>
> i also added 2 more data files to the tablespace.

It's probable that there is some pending transaction. Try to query the dba_2pc_pending view, and issue a command

    rollback force 'text';

where text is the local_tran_id or global_tran_id in the dba_2pc_pending view. Received on Sun Jan 09 2000 - 09:42:31 CST