Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: I want to know information about the session which updates a certain table
"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<419b29f6$0$1959$afc38c87_at_news.optusnet.com.au>...
> Rauf Sarwar wrote:
> > 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
>
>
> I don't think you should be encouraging use of "userenv", unless I
> missed an Oracle version from the OP. SYS_CONTEXT is the way to go.
>
> Regards
> HJR
HJR,
You are right. In 9.2 both these two built-in functions work but
USERENV is a legacy function that is retained for backward
compatibility. So I might use SYS_CONTEXT('USERENV','SESSIONID')
instead.
Thank you for your help.
Gary Received on Wed Nov 17 2004 - 16:36:29 CST