Re: v$rollstat

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 2 Jun 2004 15:22:08 +0000 (UTC)
Message-ID: <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_at_comcast.net> wrote in message
news:Li7vc.28850$IB.5808_at_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

>

>

Received on Wed Jun 02 2004 - 10:22:08 CDT