Dear Mladen, Thanks for your response.
SYS and REPADMIN users are keep on LOGGING-IN and LOGGING-OUT periodically in the background. That is why SYS user got deleted(when automatic LOG-OUT happens). Anyway I'll try auditing feature as you suggested. Thanks again, Sami -----Original Message----- Mladen Gogala Sent: Sunday, January 11, 2004 2:04 PM To: Multiple recipients of list ORACLE-L What happens is that you have a database wide logon trigger, not just a trigger on scott.schema which needs an execution context. A pseudo session with a SID=0 is established and the trigger is executed. Personally, I would use auditing ("audit session" or "audit session by scott"), not a database trigger. On 2004.01.11 12:54, Sami wrote: > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).