Logon Trigger and Cursors
- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Aug 2004 08:27:51 -0600
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
-----------------------------------------------------------------
Other related posts:
