LOGON/LOGOFF Trigger Issue

From: Sami <jaysingh1_at_optonline.net>
Date: Sun, 11 Jan 2004 09:54:27 -0800
Message-ID: <F001.005DC696.20040111095427@fatcity.com>

Hi

I am trying to maintain currently connected users list using LOGON/LOGOFF triggres.
But I am experiencing some unexpected behavior for SYS user.

CREATE OR REPLACE TRIGGER SCOTT.LOGON_TRIG AFTER LOGON ON DATABASE
BEGIN
insert into logon_info
values(SYS_CONTEXT('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','INSTANCE'), SYS_CONTEXT('USERENV','HOST'),USER,SYS_CONTEXT('USERENV', 'session_user'),sysdate);
END;
/

CREATE OR REPLACE TRIGGER SCOTT.LOGOFF_TRIG BEFORE LOGOFF ON DATABASE
BEGIN
delete logon_info where SESSIONID_NUMBER=SYS_CONTEXT('USERENV','SESSIONID'); END;
/

---

Initiated TWO session using SYS and SCOTT in a separate window

---

SQL> desc scott.logon_info

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 SESSIONID_NUMBER                                   VARCHAR2(50)
 INSTANCE_NUMBER                                    VARCHAR2(36)
 HOST_NAME                                          VARCHAR2(36)
 USER_NAME                                          VARCHAR2(36)
 SESSION_USER                                       VARCHAR2(36)
 LOGONDATE                                          DATE

SQL>set head off
SQL> set time on
19:01:01 SQL> select * from loon_info;

0
1
DELTA\SSEERANG
SYS
SYS
10-JAN-04 1411
1
DELTA\SSEERANG
SCOTT
SCOTT
10-JAN-04

---

BOTH users(SCOTT,SYS) remain connected but SESSIONID 0 disappeared.

---

19:07:05 SQL> / 1411
1
DELTA\SSEERANG
SCOTT
SCOTT
10-JAN-04 SQL> What could be the reason?

Thanks
Sami

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sami
  INET: jaysingh1_at_optonline.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Jan 11 2004 - 11:54:27 CST