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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------