We are using oracle 8174 on hp-ux . I am looking at a rollback segment with
no optimal set extended up to 2 Gigs .
I wanted to shrink to 300MB . The xacts column in v$rollstat was 1 . So i
wanted to see which session was running that
active transaction . I looked at v$transaction ( usn = xidusn) but could not
find ses_addr .
I want to know what i am missing here

Thanks

You can use this SQL
select segment_name,b.status,xacts,extents,b.rssize/1048576
rssize,aveactive/1048576 aveactive,
hwmsize/1048576 hwmsize,optsize/1048576 optsize,aveshrink/1048576
aveshrink,
gets,waits,extends,shrinks
from dba_rollback_segs a,v$rollstat b
where a.segment_id=b.usn(+)
order by 1
/
To find out dead transactions which are being cleaned out, you can use
this SQL

set echo off
col ktuxeusn head "RBS|No" form 999
col ktuxeslt head "RBS|Slot" form 9999999
col ktuxesqn head "RBS|Wrap" form 9999999

select ktuxeusn,ktuxeslt,ktuxesqn,ktuxesiz
from x$ktuxe
where ktuxecfl='DEAD'
/

regards
Srivenu

sorry,
i gave the first SQL wrong.

col sid head "Sid" form 9999 trunc
col username head "Username" form a8 trunc
col osuser head "OS User" form a7 trunc
col start_time head "Start Time" form a20
col name head "Undo|Segment" form a10
col used_ublk head "Undo|Blocks|Used" form 999999999
col used_urec head "Undo|Records" form 999999999

select s.sid,s.username,s.osuser,t.start_time,
r.name,t.xidusn,t.used_ublk,t.used_urec
from v$session s,v$transaction t,v$rollname r
where s.taddr=t.addr
and t.xidusn=r.usn
order by 1
/

regards
Srivenu

Some transactions (for example incoming distributed
queries) don't show up in v$transaction, even though
you can see a transaction address (taddr) in v$session.

You could look at the underlying X$ktcxb.
kxidusn will be the undo segment number
ktxcbxba will be the taddr from v$session

You can decode the ktcxbsta column for the
status:
decode(ktcxbsta,
0, 'IDLE',
1, 'COLLECTING',
2, 'PREPARED',
3, 'COMMITTED',
4, 'HEURISTIC ABORT',
5, 'HEURISTIC COMMIT',
6, 'HEURISTIC DAMAGE',
7, 'TIMEOUT',
9, 'INACTIVE',
10, 'ACTIVE',
11, 'PTX PREPARED',
12 ,'PTX COMMITTED',
'UNKNOWN'
) status,

and the ktcxblfg can be bit-stripped to give you
some information about the type:

decode(bitand(ktcxbflg, 2), 0, 'YES', 'NO') distributed,
decode(bitand(ktcxbflg, 16), 0, 'NO', 'YES') space,
decode(bitand(ktcxbflg, 32), 0, 'NO', 'YES') recursive,
decode(bitand(ktcxbflg, 8388608), 0, 'NO', 'YES') parallel?,


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Oradba Linux" <techiey2k3@comcast.net> wrote in message
news:Li7vc.28850$IB.5808@attbi_s04...
> We are using oracle 8174 on hp-ux . I am looking at a rollback segment
with
> no optimal set extended up to 2 Gigs .
> I wanted to shrink to 300MB . The xacts column in v$rollstat was 1 . So i
> wanted to see which session was running that
> active transaction . I looked at v$transaction ( usn = xidusn) but could
not
> find ses_addr .
> I want to know what i am missing here
>
> Thanks
>
>

Thanks a lot !!!!

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message
news:c9krb0$a70$1@sparta.btinternet.com...
>
> Some transactions (for example incoming distributed
> queries) don't show up in v$transaction, even though
> you can see a transaction address (taddr) in v$session.
>
> You could look at the underlying X$ktcxb.
> kxidusn will be the undo segment number
> ktxcbxba will be the taddr from v$session
>
> You can decode the ktcxbsta column for the
> status:
> decode(ktcxbsta,
> 0, 'IDLE',
> 1, 'COLLECTING',
> 2, 'PREPARED',
> 3, 'COMMITTED',
> 4, 'HEURISTIC ABORT',
> 5, 'HEURISTIC COMMIT',
> 6, 'HEURISTIC DAMAGE',
> 7, 'TIMEOUT',
> 9, 'INACTIVE',
> 10, 'ACTIVE',
> 11, 'PTX PREPARED',
> 12 ,'PTX COMMITTED',
> 'UNKNOWN'
> ) status,
>
> and the ktcxblfg can be bit-stripped to give you
> some information about the type:
>
> decode(bitand(ktcxbflg, 2), 0, 'YES', 'NO') distributed,
> decode(bitand(ktcxbflg, 16), 0, 'NO', 'YES') space,
> decode(bitand(ktcxbflg, 32), 0, 'NO', 'YES') recursive,
> decode(bitand(ktcxbflg, 8388608), 0, 'NO', 'YES') parallel?,
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> "Oradba Linux" <techiey2k3@comcast.net> wrote in message
> news:Li7vc.28850$IB.5808@attbi_s04...
> > We are using oracle 8174 on hp-ux . I am looking at a rollback segment
> with
> > no optimal set extended up to 2 Gigs .
> > I wanted to shrink to 300MB . The xacts column in v$rollstat was 1 . So
i
> > wanted to see which session was running that
> > active transaction . I looked at v$transaction ( usn = xidusn) but could
> not
> > find ses_addr .
> > I want to know what i am missing here
> >
> > Thanks
> >
> >
>
>