Re: JDBC connection pool and logon triggers

Mike,

This does not sound right to me either, but the contact person (who is technical) assures me that this is happening. At this point I have no reason to doubt her. BTW, I am fairly certain that these are schema logon triggers, not db logon triggers, but I am awaiting a response to verify this. If they are db logon triggers, it is a whole different story...

In a q&d test, the serial# changes if you 'connect' in sql*plus using an existing session. This also causes the logon trigger to fire, which is what I would expect.

SQL> connect / as sysdba
Connected.
SQL> @cr_test_trig
SQL> create or replace trigger test_trigger
  2  after logon on cbo_test.schema
  3  begin
  4     insert into test_login (login_date) values (sysdate);
  5  end;
  6  /

Trigger created.

SQL> select count(*) from test_login;

  COUNT(*)
----------
         0

SQL> select sid, serial#, schemaname from v$session where type = 'USER';

       SID    SERIAL# SCHEMANAME
---------- ---------- ------------------------------
         7          1 SYS
        10       2164 SYS

SQL> connect cbo_test/cbo_test
Connected.
SQL> select count(*) from test_login;

  COUNT(*)
----------
         1 <===== Trigger has fired

SQL> select sid, serial#, schemaname from v$session where type = 'USER';

       SID    SERIAL# SCHEMANAME
---------- ---------- ------------------------------
         7          1 SYS
        10       2166 CBO_TEST

SQL> connect bca/bca
Connected.
SQL> select count(*) from test_login;

  COUNT(*)
----------
         1 <===== Trigger has NOT fired

SQL> select sid, serial#, schemaname from v$session where type = 'USER';

       SID    SERIAL# SCHEMANAME
---------- ---------- ------------------------------
         7          1 SYS
        10       2168 BCA

SQL> connect cbo_test/cbo_test
Connected.
SQL> select count(*) from test_login;

  COUNT(*)
----------
         2 <===== Trigger has fired again

SQL> select sid, serial#, schemaname from v$session where type = 'USER';

       SID    SERIAL# SCHEMANAME
---------- ---------- ------------------------------
         7          1 SYS
        10       2170 CBO_TEST

SQL> connect cbo_test/cbo_test
Connected.
SQL> select count(*) from test_login;

  COUNT(*)
----------
         3 <===== Trigger has fired again, even though the username is the 
same...but Oracle would not know that...

SQL> select sid, serial#, schemaname from v$session where type = 'USER';

       SID    SERIAL# SCHEMANAME
---------- ---------- ------------------------------
         7          1 SYS
        10       2172 CBO_TEST


So, my conclusion is that the jdbc connection pool logic the application is using is issuing a reconnect statement on each reuse of the persistent connection. Being the java-challenged dba I am, is this how a normal jdbc connection pool behaves?

Regards,

Daniel Fink

Michael Thomas wrote:
Dan,

Maybe I'm ignorant, since I don't have the time to
test right now, but that does *not* sound right to me.
And, I do not remember seeing this so my ignorance
proves my hypothesis. Sort of like two wrongs make a
right. ;-)

The "SERIAL#" field in v$session is intended to
uniquely distinguish a SID, e.g. in case the
connection is *closed* and the SID value re-used.

My Blasphemy Caused by Hypothetical Results (BCHR):
Therefore, based on Oracle's implementation of JDBC2.0
your "connection pool" session should not actually
close and the "SERIAL#" should not change.

Maybe your Java developers are actually closing the
connections before handing off to the next process.

Okay, now throw the rocks (since I didn't test it).
:-)

Regards,

Mike Thomas 

----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: