What stupid mistake am I making with this onlogin trigger?
- From: <Jay.Miller@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Feb 2008 15:34:00 -0500
After going back an forth on this for a while today I figured a few more
sets of eyes couldn't hurt.
I need to track connections from a specific module (not SQL*Plus but
that was easiest for testing purposes). This trigger compiles but does
not create any rows. The insert statement works if run from a sql*plus
prompt. I'm sure I'm missing something really obvious here...
Does the onlogin trigger possibly execute as the user logging in as
opposed to as sys?
Trigger created:
SQL> create or replace trigger sys.trace_php_onlogin after logon on
database
2 begin
3
4 insert into wsidba.php_logins
5 select sid,username,module
6 from sys.v_$session
7 where sid = (select unique(sid) from v$mystat)
8 and upper(module) like '%SQL%';
9 commit;
10
11 end;
12 /
Trigger created.
Doesn't enter any data to table:
usnjc01urp001:/services/home/oracle<lass> $ sqlplus wsidba
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:23:43 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> select * from wsidba.php_logins;
no rows selected
Works from SQL*Plus:
$ sqlplus wsidba
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:25:58 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> insert into wsidba.php_logins
select sid,username,module
from sys.v_$session
where sid = (select unique(sid) from v$mystat)
and upper(module) like '%SQL%'; 2 3 4 5
1 row created.
TIA,
Jay Miller
Other related posts:
