Re: Active rollback with no sessions

From: <markp7832_at_my-deja.com>
Date: Wed, 12 Jan 2000 16:25:47 GMT
Message-ID: <85i9tn$6pm$1@nnrp1.deja.com>

In article <387A83B5.1134CF3B_at_cis.com.ph>,   Michael Puente <mcpuente_at_cis.com.ph> wrote:
> kill the session using the rollback segment or shutdown the database
and
> mount it.
>
> Kimon Andreou wrote:
>
> > 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.
> >
> > any ideas?
> >
> > thanks
> >
> > Kimon Andreou
> >
>

Here is a script to try to find active users of rbs segments set echo off
rem
rem file:
rem SQL*Plus script to display sessions using a rollback segment. rem
rem 19960522 M D Powell New script to see rbs users. rem
rem
rem xidsqn - matches id2 column of v$lock rem

column usn      format 9999
column name     format a12

column username format a12

select r.name, usn, xidslot, xidsqn, s.sid, s.username from v$rollname r, v$transaction t, v$session s where r.usn = t.xidusn
and t.addr = s.taddr
order by r.name
/

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 10:25:47 CST