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: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 17 Nov 2004 21:37:43 +1100
Message-ID: <419b29f6$0$1959$afc38c87@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 Received on Wed Nov 17 2004 - 04:37:43 CST

Original text of this message

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