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

From: Gary <rooty_hill2002_at_yahoo.com.au>
Date: 16 Nov 2004 17:23:45 -0800
Message-ID: <171bd226.0411161723.5998e6f1@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 Received on Tue Nov 16 2004 - 19:23:45 CST