Hi guys,

I am learning about DRCP and proxy users with a view to incorporating them both into our applications. As a result, I have the following two questions. I hope you can help!!

Question 1:

When using the database resident connection pooling functionality I observe the following behaviour.

1) Switch on the connection pool with the default values using DBMS_CONNECTION_POOL.start_pool();
2) Alter the TNS connection details to add the SERVER=POOLED
3) Successfully connect to the database using the modified TNS connection details.

All good.... but,

If the MAX_THINK_TIME parameter (default 120) is exceeded (i.e. no commands are issued by the pooled session), the session will terminate and produce an ORA-03113: end-of-file on communication channel. I believe that this is documented but there seems to be a nasty side effect:

Suppose, as part of the application, a sequence was defined with the caching option of 2,000 numbers. I have noticed that whenever a pooled session is terminated in this way, the sequence discards the unused numbers in the cache - almost as if the instance is restarting as a result. I have scanned the on-line manuals and I can't find this behaviour documented anywhere. If this is indeed the desired behaviour, can this behaviour be overridden (apart from increasing the MAX_THINK_TIME parameter)?

Question 2:

When using the proxy user functionality, is there a way of specifying the proxy user password on the command line for SQL*Plus? It is well documented that the proxy user can be specified using the following syntax:

$ sqlplus /nolog
SQL> conn web[mark]/web@testpdb

It would follow that the password for mark could be added using the following syntax but it doesn't seem to work:
SQL> conn web[mark/mark]/web@testpdb

I also use Pro*C and couldn't find a similar extension to the EXEC SQL CONNECT to allow proxy users either.

Many thanks in advance
Mark.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
    200013

[wait]

SQL> /
select seq.nextval from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2336
Session ID: 216 Serial number: 31927


SQL> conn mcdonac/xxxxxx@np12_pooled
Connected.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
    200014