RE: What stupid mistake am I making with this onlogin trigger?

Many thanks to Paul Drake for suggesting I set role none; in the
sql*plus session to duplicate what was happening in the trigger.  That
let me identify the problem (although I had granted v_$session directly
I had missed v$mystat). 
 
This was my first trigger written in several years so I don't feel too
guilty calling in help.
 

Jay Miller 
Sr. Oracle DBA 
201.369.8355 

 

________________________________

From: Miller, Jay 
Sent: Monday, February 25, 2008 3:34 PM
To: oracle-l
Subject: What stupid mistake am I making with this onlogin trigger? 



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: