clearing session state?

Not sure if I'm off the deep end here or not...

I'm writing a .NET (1.1) based application server that connects to Oracle (9.2) using connection pooling.

The nature of the .NET connection pooling seems to be that when a connection is "closed" and then reopened (returned from the pool), the session state remains intact for the new use of the connection.

Specifically, things like sys_context values and rows in global temporary tables (with on commit preserve rows) remain in tact and visible to session that is associated with the "new" connection returned from the pool.

This leaves me with just a few options, as far as I can tell:

1. Do not use any session state dependant values for anything.
2. Explicitly initialize all session values each time a connection is obtained from the pool. Also, clear/truncate any temporary tables that are used.
3. Do not use connection pooling.

I'm tempted to opt for #1, just out of frustration. However, I'm pretty sure that I'm going to need to take advantage of some of these mechanisms as I try to implement the more elaborate functionality my application needs to provide.

#3 is bad news. My app manages hundreds of users, all of which need database connectivity occasionally and briefly. Connection pooling is a very good thing for me.

This leaves #2 which, though it ain't brilliant, seems plausible.

The question then is whether there is some nice oracle mechanism (sql, plsql, dbms_* call, ?) to clear all such session state, or do I have to clear all values, and temporary tables explicitly?

Hope this made sense.

Please feel free to offer any alternatives that I haven't described. I can't be the first one over this ground.

Thanks. Received on Fri Oct 31 2003 - 15:09:50 CST