Hmm ... I'm still thinkin' that it logs all the sessions and commits without
autonomous transaction:
16:00:07 SQL> CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE
16:00:18 2 BEGIN
16:00:18 3 execute immediate
16:00:18 4 'alter session set events ''10046 trace name context
forever,level 12'' ';
16:00:24 5 insert into sys.log_info
16:00:24 6 values(sysdate,ora_client_ip_address,ora_login_user);
16:00:24 7 END;
16:00:24 8 /
Trigger created.
Elapsed: 00:00:00.31
16:00:25 SQL> conn test/test_at_dbarepo1
Connected.
16:00:32 SQL> select 'X' from dual;
'
-
X
Elapsed: 00:00:00.01
16:00:42 SQL> exit
I think the first commit is here(rlbk=0):
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0
w=0 time=99 us)'
XCTEND rlbk=0, rd_only=0 -----> !!!
WAIT #0: nam='log file sync' ela= 22244 p1=7 p2=0 p3=0
$ view dbarepo1_ora_13832.trc
"dbarepo1_ora_13832.trc" [Read only] 210 lines, 11911 characters
/app/oracle/admin/dbarepo1/udump/dbarepo1_ora_13832.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /app/oracle/product/9i
System name: SunOS
Node name: ess038
Release: 5.8
Version: Generic_117350-02
Machine: sun4u
Instance name: dbarepo1
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 13832, image: oracle_at_ess038 (TNS V1-V3)
- SESSION ID:(10.512) 2005-06-09 16:00:32.019
APPNAME mod='sqlplus_at_ess038 (TNS V1-V3)' mh=0 act='' ah=0
PARSING IN CURSOR #2 len=69 dep=2 uid=0 oct=42 lid=0 tim=7017570116495
hv=3841573810 ad='2ef4ce98'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #2:c=0,e=5064,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=7017570109425
PARSING IN CURSOR #2 len=77 dep=2 uid=0 oct=2 lid=0 tim=7017570171198
hv=3939809092 ad='2dcbfa40'
INSERT into sys.log_info
values(sysdate,ora_client_ip_address,ora_login_user)
END OF STMT
PARSE
#2:c=30000,e=54187,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=7017570171180
BINDS #2:
EXEC #2:c=0,e=29846,p=0,cr=1,cu=7,mis=0,r=1,dep=2,og=4,tim=7017570201411
PARSING IN CURSOR #1 len=182 dep=1 uid=0 oct=47 lid=0 tim=7017570201634
hv=2221084585 ad='2ef55a24'
BEGIN
execute immediate
'alter session set events ''10046 trace name context forever,level 12'' ';
insert into sys.log_info
values(sysdate,ora_client_ip_address,ora_login_user);
END;
END OF STMT
EXEC #1:c=30000,e=98628,p=0,cr=1,cu=7,mis=1,r=1,dep=1,og=4,tim=7017570201619
WAIT #0: nam='log file sync' ela= 24885 p1=5 p2=0 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 1261 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 177 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 392 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 247 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 370 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #1 len=22 dep=0 uid=24 oct=3 lid=24 tim=7017570243386
hv=4119976668 ad='2dddf5ac'
SELECT USER FROM DUAL
END OF STMT
PARSE #1:c=0,e=1991,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7017570243373
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 445 p1=1413697536 p2=1 p3=0
BINDS #1:
EXEC #1:c=0,e=131,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570244540
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 196 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=332,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=7017570245287
WAIT #1: nam='SQL*Net message from client' ela= 213 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #2 len=116 dep=1 uid=0 oct=3 lid=0 tim=7017570247066
hv=431456802 ad='2df28dd8'
select
o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags
from obj$ o where o.obj#=:1
END OF STMT
PARSE #2:c=0,e=1169,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=7017570247054
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24
offset=0
bfp=feb6b8b4 bln=22 avl=03 flg=05
value=222
EXEC #2:c=0,e=5986,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570253476
FETCH #2:c=0,e=89,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=7017570253667
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0
w=0 time=190 us)'
WAIT #0: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 436 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #1 len=40 dep=0 uid=24 oct=3 lid=24 tim=7017570272481
hv=62978080 ad='2ddd8554'
SELECT NULL FROM DUAL FOR UPDATE NOWAIT
END OF STMT
PARSE #1:c=0,e=10309,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7017570272468
BINDS #1:
EXEC #1:c=0,e=501,p=0,cr=3,cu=2,mis=0,r=0,dep=0,og=4,tim=7017570273272
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 7525 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=0 r=0 w=0 time=0
us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0
w=0 time=81 us)'
PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=7017570289615
hv=3468666020 ad='2de2fce4'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=0,e=679,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=7017570289602
BINDS #2:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=16
offset=0
bfp=feb6cf8c bln=16 avl=16 flg=05
value=0000146B.0012.0001
EXEC #2:c=0,e=664,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570290575
FETCH #2:c=0,e=74,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=7017570290747
PARSING IN CURSOR #3 len=116 dep=2 uid=0 oct=3 lid=0 tim=7017570291201
hv=431456802 ad='2df28dd8'
select
o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags
from obj$ o where o.obj#=:1
END OF STMT
PARSE #3:c=0,e=264,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=7017570291189
BINDS #3:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24
offset=0
bfp=feb69928 bln=22 avl=02 flg=05
value=62
EXEC #3:c=0,e=249,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=7017570291838
FETCH #3:c=0,e=63,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=7017570291991
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$
(cr=1 r=0 w=0 time=45 us)'
PARSING IN CURSOR #1 len=161 dep=0 uid=24 oct=3 lid=24 tim=7017570299877
hv=3096433403 ad='2ddd5930'
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM
SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUC
T)) AND (UPPER(USER) LIKE USERID)
END OF STMT
PARSE
#1:c=10000,e=18160,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=4,tim=7017570299863
BINDS #1:
EXEC #1:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570300268
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=81,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570300545
WAIT #1: nam='SQL*Net message from client' ela= 610 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #2 len=116 dep=1 uid=0 oct=3 lid=0 tim=7017570301820
hv=431456802 ad='2df28dd8'
select
o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags
from obj$ o where o.obj#=:1
END OF STMT
PARSE #2:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570301808
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24
offset=0
bfp=feb699a0 bln=22 avl=03 flg=05
value=6253
EXEC #2:c=0,e=243,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570302488
FETCH #2:c=0,e=72,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=7017570302692
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=6253 op='TABLE ACCESS FULL
SQLPLUS_PRODUCT_PROFILE (cr=3 r=0 w=0 time=73 us)'
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 205 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=7017570304529
hv=3468666020 ad='2de2fce4'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570304516
BINDS #2:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=16
offset=0
bfp=feb6cf8c bln=16 avl=16 flg=05
value=0000146B.0012.0001
EXEC #2:c=0,e=234,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7017570305052
FETCH #2:c=0,e=59,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=7017570305203
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$
(cr=1 r=0 w=0 time=34 us)'
PARSING IN CURSOR #1 len=183 dep=0 uid=24 oct=3 lid=24 tim=7017570306550
hv=2009857449 ad='2dd2d330'
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE
UPPER(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR
(USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
END OF STMT
PARSE #1:c=0,e=2928,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=4,tim=7017570306537
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 258 p1=1413697536 p2=1 p3=0
BINDS #1:
EXEC #1:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570307488
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 167 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=69,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570307940
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 166 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=6253 op='TABLE ACCESS FULL
SQLPLUS_PRODUCT_PROFILE (cr=3 r=0 w=0 time=64 us)'
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 417 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #1 len=54 dep=0 uid=24 oct=47 lid=24 tim=7017570310717
hv=1432236634 ad='2dd2ab50'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=0,e=1499,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=7017570310703
BINDS #1:
bind 0: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=32
offset=0
bfp=feb5f830 bln=32 avl=08 flg=05
value="SQL*Plus"
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
EXEC #1:c=10000,e=17649,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=7017570328584
WAIT #1: nam='SQL*Net message from client' ela= 333 p1=1413697536 p2=1 p3=0
PARSING IN CURSOR #1 len=40 dep=0 uid=24 oct=3 lid=24 tim=7017570331112
hv=4282642546 ad='2dd29388'
SELECT DECODE('A','A','1','2') FROM DUAL
END OF STMT
PARSE #1:c=0,e=1764,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7017570331099
BINDS #1:
EXEC #1:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7017570331496
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=145,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=7017570331825
WAIT #1: nam='SQL*Net message from client' ela= 554 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0
w=0 time=99 us)'
XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 22244 p1=7 p2=0 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
- 2005-06-09 16:00:42.333
WAIT #0: nam='SQL*Net message from client' ela= 9826344 p1=1413697536 p2=1
p3=0
PARSING IN CURSOR #1 len=20 dep=0 uid=24 oct=3 lid=24 tim=7017580183675
hv=1477081359 ad='2ef39fdc'
select 'X' from dual
END OF STMT
PARSE #1:c=0,e=1372,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7017580183659
BINDS #1:
EXEC #1:c=0,e=221,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7017580184152
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=159,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=7017580184511
WAIT #1: nam='SQL*Net message from client' ela= 651 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=7017580185440
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 2234471 p1=1413697536 p2=1
p3=0
XCTEND rlbk=0, rd_only=1
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL OBJ#(222) (cr=3
r=0 w=0 time=134 us)'
But, it commits even if the user issues rollback or fail to exit correctly:
16:05:36 SQL> select * from sys.log_info;
no rows selected
Elapsed: 00:00:00.01
16:05:49 SQL> conn test/test_at_dbarepo1;
Connected.
16:05:58 SQL> conn / as sysdba
Connected.
16:06:03 SQL> select * from sys.log_info;
LOGIN_DAT IPADDS USERNAME
--------- -------------------- ------------------------------
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
Elapsed: 00:00:00.00
16:06:04 SQL> conn test/test_at_dbarepo1
Connected.
16:06:12 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
16:06:17 SQL> conn / as sysdba
Connected.
16:06:20 SQL> select * from sys.log_info;
LOGIN_DAT IPADDS USERNAME
--------- -------------------- ------------------------------
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
Elapsed: 00:00:00.00
16:06:24 SQL> conn test/test_at_dbarepo1
Connected.
16:08:19 SQL> Killed
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jun 9 16:08:39 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
16:08:40 SQL> select * from sys.log_info;
LOGIN_DAT IPADDS USERNAME
--------- -------------------- ------------------------------
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
09-JUN-05 10.44.19.31 TEST
09-JUN-05 SYS
6 rows selected.
Elapsed: 00:00:00.01
Cheers
Dimitre Radoulov
ETNØTEAM
- Original Message -----
From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com>
To: <oracle-l_at_freelists.org>
Sent: Thursday, June 09, 2005 3:53 PM
Subject: RE: Trigger not firing!!!!!!!!!!(URGENT)
> Hi Dimitre,
>
> Some programs may auto-commit or auto-rollback on disconnect. Check a
> 10046 trace to see where the COMMIT happens. The row stays, so
> logically it must happen somewhere in the connection, right?
>
> Oooh, a LOGON trigger for a 10046 trace! :)
>
> Rich
>
> -----Original Message-----
> From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com]=20
> Sent: Thursday, June 09, 2005 8:38 AM
> To: Jesse, Rich; oracle-l_at_freelists.org
> Subject: Re: Trigger not firing!!!!!!!!!!(URGENT)
>
>
>> 3) Wouldn't it be "better" if a procedure with a PRAGMA
>> AUTONOMOUS_TRANSACTION were called to do this, including the
> obligitory
>> COMMIT, thereby helping ensure that all connections (not just from the
>> SQL*Plus program) are logged?
>
> Why do you think that only the sqlplus's sessions will be logged?
>
>
> Regards
> Dimitre Radoulov
> --
> http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 09 2005 - 10:19:02 CDT
