Logon Trigger and Cursors

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 25 Aug 2004 08:27:51 -0600
Message-id: <412CA1E7.2000107@sun.com>

I've come across a situation that I am at a loss to explain. (and no medical catastrophe analogies here).

I have a logon trigger to capture session information (poor man's audit). In the 10046 trace file, I see the cursors assoctiated with the trigger, but they do not produce STAT lines. I assume the cursor is closed, because the handle is reused. When I execute the trigger body by itself, I get STAT lines. When I execute a DML trigger, I get STAT lines.

The 'rule' I've operated with is that STAT lines are emitted when the cursor is closed. Is there an exception to this rule, something I don't know or perhaps a bug?

The 10046 output is below (some info snipped). The database is 9.2.0.3/64-bit on Solaris9.

Regards,
Daniel Fink

#1 - Logon Trigger

PARSING IN CURSOR #1 len=520 dep=1 uid=0 oct=47 lid=0 tim=3524087815388 hv=3866665090 ad='bbc999c0'
declare

   v_session_id number;
   v_osuser varchar2(64);
   v_machine varchar2(64);
   v_program varchar2(64);

begin
if user != 'CDP_USER'
then

     select sid into v_session_id from sys.v$mystat where rownum = 1;
     select osuser, machine, program
     into   v_osuser, v_machine, v_program
     from sys.v$session
     where sid = v_session_id;
     insert into user_connections
       (os_username, user_machine, user_program)
     values
       (v_osuser, v_machine, v_program);

end if;
end;
END OF STMT

PARSING IN CURSOR #2 len=25 dep=2 uid=0 oct=3 lid=0 tim=3524087816234 hv=1817891629 ad='c2779768'
SELECT user from sys.dual
END OF STMT

PARSING IN CURSOR #3 len=45 dep=2 uid=0 oct=3 lid=0 tim=3524087817187 hv=2517005314 ad='b9b942e8'
SELECT sid from sys.v$mystat where rownum = 1 END OF STMT

PARSING IN CURSOR #4 len=78 dep=2 uid=0 oct=3 lid=0 tim=3524087817873 hv=1814484206 ad='b8facbb8'
SELECT osuser, machine, program

     from sys.v$session
     where sid = :b1

END OF STMT

PARSING IN CURSOR #5 len=115 dep=2 uid=0 oct=2 lid=0 tim=3524087818578 hv=2037040652 ad='bb418ef0'
INSERT into user_connections

       (os_username, user_machine, user_program)
     values
       (:b3, :b2, :b1)

END OF STMT

Notice that #3 is reused, but there are not any STAT lines from the previous incarnation.

PARSING IN CURSOR #3 len=22 dep=0 uid=34 oct=3 lid=34 tim=3524087998886 hv=4119976668 ad='bceed600'
SELECT USER FROM DUAL
END OF STMT
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=65 us)'

PARSING IN CURSOR #1 len=31 dep=0 uid=34 oct=47 lid=34 tim=3524088221433 hv=2753068986 ad='c6c6feb8'
begin dbms_output.disable; end;
END OF STMT

PARSING IN CURSOR #3 len=242 dep=0 uid=34 oct=3 lid=34 tim=3524088266374 hv=2209720083 ad='bb28c5d8'
select parameter, value from v$nls_parameters where (upper(parameter) in ('NLS_SORT','NLS_CURRENCY','NLS_ISO_CUR

RENCY',                   
'NLS_DATE_LANGUAGE','NLS_NUMERIC_CHARACTERS',

'NLS_LANGUAGE','NLS_TERRITORY'
))
END OF STMT

Notice that #3 is closed (STAT) and then immediately reused.

STAT #3 id=1 cnt=7 pid=0 pos=1 obj=76 op='FIXED TABLE FULL X$NLS_PARAMETERS (cr=0 r=0 w=0 time=168 us)'

PARSING IN CURSOR #3 len=97 dep=0 uid=34 oct=3 lid=34 tim=3524088355850 hv=3119068789 ad='c150f078'
select value from v$nls_parameters where (upper(parameter) = 'NLS_DATE_FORMAT')
END OF STMT #2 - Executing trigger body in sql*plus

PARSING IN CURSOR #1 len=504 dep=0 uid=0 oct=47 lid=0 tim=3528329298842 hv=645320079 ad='c7a9c178'
declare

  v_session_id number;
  v_osuser varchar2(64);
  v_machine varchar2(64);
  v_program varchar2(64);

begin
if user != 'CDP_USER'
then

    select sid into v_session_id from sys.v$mystat where rownum = 1;     select osuser, machine, program
    into v_osuser, v_machine, v_program     from sys.v$session
    where sid = v_session_id;
    insert into user_connections
      (os_username, user_machine, user_program)     values
      (v_osuser, v_machine, v_program);
end if;
end;
END OF STMT

PARSING IN CURSOR #2 len=25 dep=1 uid=0 oct=3 lid=0 tim=3528329299683 hv=1817891629 ad='c2779768'
SELECT user from sys.dual
END OF STMT

PARSING IN CURSOR #3 len=45 dep=1 uid=0 oct=3 lid=0 tim=3528329300578 hv=2517005314 ad='b9b942e8'
SELECT sid from sys.v$mystat where rownum = 1 END OF STMT

PARSING IN CURSOR #4 len=76 dep=1 uid=0 oct=3 lid=0 tim=3528329301200 hv=3763011895 ad='bf79c710'
SELECT osuser, machine, program

from sys.v$session
where sid = :b1
END OF STMT

PARSING IN CURSOR #5 len=112 dep=1 uid=0 oct=2 lid=0 tim=3528329301969 hv=1890800810 ad='c57f5b70'
INSERT into user_connections

(os_username, user_machine, user_program) values
(:b3, :b2, :b1)
END OF STMT

Notice that #3 and #4 are closed (indicated by the STAT lines)

STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=0 r=0 w=0 time=67 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='FILTER (cr=0 r=0 w=0 time=61 us)' STAT #3 id=3 cnt=1 pid=2 pos=1 obj=26 op='FIXED TABLE FULL X$KSUMYSTA (cr=0 r=0 w=0 time=43 us)'
STAT #3 id=4 cnt=1 pid=2 pos=2 obj=24 op='FIXED TABLE FULL X$KSUSGIF (cr=0 r=0 w=0 time=3 us)'
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=16 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 r=0 w=0 time=35 us)'

PARSING IN CURSOR #1 len=18 dep=0 uid=0 oct=3 lid=0 tim=3528336949574 hv=1333943659 ad='bf4f1df8'
select * from dual
END OF STMT
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=195 op='TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=150 us)'

PARSING IN CURSOR #1 len=55 dep=0 uid=0 oct=42 lid=0 tim=3528351632474 hv=4110456808 ad='caa931f8'
alter session set events '10046 trace name context off' END OF STMT

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 25 2004 - 09:25:05 CDT