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

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 Received on Wed Nov 17 2004 - 04:03:52 CST