Re: JDBC connection pool and logon triggers

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 28 May 2004 11:05:48 -0600
Message-id: <40B7716C.8090409@sun.com>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri May 28 2004 - 12:04:49 CDT