Logs information about the pit where session info is not updated when connecting to Oracle using the ADO connection pool

In a recent project, because of the large amount of data in the interface query, the associated table is more, some of the data to be temporarily saved for use in subsequent queries, so think of using Oracle's temporary table to achieve this requirement. As you all know, there are two types of temporal tables for Oracle: Transaction-level temporary tables and session-level temporary tables, which I am using at conversation-level staging tables. At that time after the function of the thought is all right, did not expect to buy a pit here.

the emergence of a pit:Then, when debugging logs for the system accidentally found that the data of the temporary table is not as defined as the Oracle temporary table, "different sessions exclusive temporary table, temporary table data at the end of the session is automatically emptied." First of all, the first query of the log records, the first query data is 10017 rows, the red box is used to the temporary table: the second query of the log records, the second query the amount of data less than the first, 15 rows: From the previous two times the results of the query, The conclusion is that the Oracle session-level temporary table used does not have the data of the temporary table emptied as it was defined at the end of the session? But obviously not for this reason, most likely is that the reason is that the two queries are the same session, so that the temporary table data is not emptied. With this idea, the next step is to find out why the query before and after two times is the same session. The reason why the pit was held:First, the system environment: 1, the use of ADO is enabled by default connection pooling, connection pooling configuration using the default configuration, 2, the drive to connect to the Oracle database is: 3, each query is a new connection, and then all after the query is finished calling close (), Dispose ()； find the idea of a pit:1, the connection pool is enabled, the connection used by the two queries is the same connection, 2, after the query is finished, connection calls the close (), Dispose () method does not really close the session; validation Process: First look at the Oracle official documentation for Connection pool interpretation:with connection pooling enabled  (The default),  the open and close methods of the oracleconnection object  implicitly use the connection pooling service. in the preceding  Code, the open call uses the connection pooling service, which  is responsible for returning a connection to the application.  connection pools are created by the connection pooling service  using the connectionstring as a signature to uniquely identify  a pool. if no pool with the exact attribute values in  the connectionstring exists, the connection pooling service creates  a new connection pool. if a pool already exists with the requested  signature, a connection is returned to the application from  Thatpool. when a connection pool is created, the connection-pooling  service initially creates the number of connections defined by  the Min Pool Size attribute of the ConnectionString. This  number of connections is always maintained by the connection  pooling service for the connection pool. at any given time,  These connections are available in the pool or used by the  application. The Incr Pool Size attribute of the  Connectionstring defines the numbeR of new connections to be created by the connection pooling  service when more connections are needed in the connection  pool. when the application closes a connection, the connection  pooling service determines whether the connection lifetime has  Exceeded the connection lifetime attribute; if so, the connection  pooling service closes the connection; otherwise, the connection  goes back to the connection pool. The connection pooling  service only enforces the connection lifetime when a connection  is going backto the connection pool. the max pool size  Attribute of the connectionstring sets the maximum number of connections for a  Connection pool. if a new connection is requested, no connections  are available, and max pool size has been reached, then  the connection pooling service waits for the time defined  By connection timeout. if the connection timeout has been reached  and there are still no connectionsavailable in the pool,  The connection pooling service raises an exception indicating that  the pooled connection request has timed-out. The connection  pooling service closes connections when they are not used;  Connections are closed every three minutes. the decr pool size attribute of the  connectionstring provides connection pooling service for the maximum  number of connections that can be closed in one run.  When connection calls the open () method, the following is the official Oracle documentation Description: The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established. It is possible, the pool does not contain any unused connections when the  Open()method is invoked. In this case, a new connection is established. When connection calls the close () method, the following is the official Oracle documentation Description: Rolls back any pending transactions.
Places the connection to the connection pool if connection pooling is enabled. Even if connection pooling is enabled, the connection can being closed if it exceeds the connection lifetime specified in the Connection string. If connection pooling is disabled, the connection is closed.
Closes the connection to the database.

The connection can be reopened using Open() .

From the Oracle official documentation for the description of the open and close methods of connection pool and connection and in conjunction with the current system environment (query request only one client), it is not difficult for us to get such a scenario: when the first query, Connection Pool Creates a connection returned to the Connection object, which is returned to the connection pool when the query is completed, and when the second query is less than the default Connection with the first query interval lifetime,connection Pool returns the connection used for the first query to the connection object for querying, resulting in the first and second connections being the same. Again, by the description of the close () method, when connection calls the Close method, it does not erase data such as the connected session information (only if the connection is actually removed from the connection pool). There are two solutions currently in mind (final decision to use the first one): 1, truncate the temporary table before each use of the temporary table, 2, do not use connection Pool; The first post, mainly today read the Bo Master Nong Code a lifetime blog, "Why should we insist on blogging", Then by the way to record this pit, ask the garden seniors, there is no other solution, or say, I solve the problem in the wrong direction. Please advise ~ ~ ~

Logs information about the pit where session info is not updated when connecting to Oracle using the ADO connection pool