Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: I want to know information about the session which updates a certain table

Re: I want to know information about the session which updates a certain table

From: Gary <rooty_hill2002_at_yahoo.com.au>
Date: 17 Nov 2004 14:31:31 -0800
Message-ID: <171bd226.0411171431.438d5bf0@posting.google.com>


rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0411170203.6b65853b_at_posting.google.com>...
> rooty_hill2002_at_yahoo.com.au (Gary) wrote in message news:<171bd226.0411161723.5998e6f1_at_posting.google.com>...
> > Hi!
> >
> > I have a table "AccountStatus" that is updated about 10000 times a
> > day.
> >
> > Database version : 9.2.0.4
> >
> > Platform : HP-UX 11
> >
> > Database name: reportsq (same as instance name)
> >
> > Monitored table: AccountStatus
> > create table AccountStatus
> > (
> > AccountNo Number(10), ---- Account Number
> > Status varchar2(1), ---- Status field being
> > updated
> > OtherDetails varchar2(100)
> > );
> >
> > This table has a "status" field which can be in 5 different values
> > starting from NULL, to "D" then "V" then "A" and finally "K"
> > sequentially. For some reason, rarely the status can change from any
> > non-Null value back to Null which causes problem in the frontend(The
> > developers are claiming this, I am just trying to find out the truth).
> > Now, I want to monitor this abnormalty. I am trying to write a trigger
> > on this table to write some session information into a another little
> > audit table when a "status" is changed from any non-Null value to
> > Null(i.e. from "A" to Null when it is supposed to "K").
> >
> > The question is, in this trigger, how can I find out which session is
> > doing the update?
> >
> > First of all, I can get the user of this session:
> >
> > SQL> select user from dual;
> >
> > USER
> > ------------------------------
> > TESTER
> >
> > Then, if all connected sessions using unique userids, I suppose I can:
> >
> > SQL> select s.username, s.sid, p.spid from v$session s, v$process p
> > where s.paddr=p.addr and sid>10 order by username;
> >
> > USERNAME SID SPID
> > ------------------------------ ---------- ------------
> > COLLECTIONQ 14 23775
> > CONTACTDBA 11 21596
> > SYS 38 13606
> > TESTER 52 6487
> >
> > 4 rows selected.
> >
> > Then I can find out the further session information from v$session
> > then update some audit table.
> >
> > However, the front end application may share same userid "TESTER" to
> > sign on the database and update this table in the same time
> > (I know it's bad,but I can't change this at the moment).
> >
> > So, when I :
> >
> > SQL> select s.username, s.sid, p.spid from v$session s, v$process p
> > where s.paddr=p.addr and username='TESTER';
> >
> > USERNAME SID SPID
> > ------------------------------ ---------- ------------
> > TESTER 52 6487
> > TESTER 67 14598
> > TESTER 35 14026
> > TESTER 71 22999
> > TESTER 37 16713
> > TESTER 24 26377
> > TESTER 41 15601
> > TESTER 59 1668
> > TESTER 18 16592
> >
> > 9 rows selected.
> >
> > From the trigger, how can I tell which SID/SPID is updating
> > AccountStatus?
> >
> > Thanks in advance,
> >
> > Gary
>
> USERENV('SESSIONID') returns the unique session id of the logged on
> user which maps to v$session.audsid. So to get to the actual logged on
> session you can,
>
> select *
> from v$session
> where audsid = userenv('SESSIONID');
>
> Regards
> /Rauf

Rauf,

After I posted my original message, I found out this answer from MetaLink. Thanks a lot for your help anyway.

Gary Received on Wed Nov 17 2004 - 16:31:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US