Re: JDBC connection pool and logon triggers

  • From: Daniel Fink <Daniel.Fink@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 28 May 2004 11:05:48 -0600

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2004