最近的一個項目中，由於界面查詢的數據量比較大，關聯的表比較多，有些數據查出來需要臨時保存起來供後面的查詢使用，於是想到了用oracle的臨時表來實現這個需求。大家都知道，oracle的臨時表有兩種：事務級別臨時表和會話級別臨時表，我這裡使用的是會話級別的臨時表。當時把功能時候後就以為萬事大吉了，沒想 ...
最近的一個項目中，由於界面查詢的數據量比較大，關聯的表比較多，有些數據查出來需要臨時保存起來供後面的查詢使用，於是想到了用oracle的臨時表來實現這個需求。大家都知道，oracle的臨時表有兩種：事務級別臨時表和會話級別臨時表，我這裡使用的是會話級別的臨時表。當時把功能時候後就以為萬事大吉了，沒想到就在這裡買下了一個坑。
坑的浮現：之後在為系統加調試日誌時偶然發現了臨時表的數據沒有像oracle臨時表的定義那樣“不同會話獨享臨時表，臨時表的數據在會話結束後被自動清空”。首先看第一次查詢的日誌記錄截圖，第一次的查詢數據量是10017行，紅色框圈住的地方使用到臨時表：
第二次查詢的日誌記錄截圖，第二次查詢的數據量比第一次少，15行：
從這前後兩次的查詢結果來看，得到的結論是：使用到的oracle會話級別臨時表沒有像它定義那樣，在會話結束後沒有把臨時表的數據清空？不過很明顯不是因為這個原因了，最有可能的就是原因應該是，前後兩次查詢都是同一個Session，所以才導致臨時表的數據沒有被清空了。有了這個思路，接下來就是找到為什麼前後兩次的查詢會是同一個Session。
追究坑出現的原因：
首先，系統環境：
1、使用的ADO.NET是預設啟用了連接池，連接池配置使用預設的配置；
2、連接oracle資料庫的驅動是：
3、每次查詢都是新建一個Connection，然後都是在查詢完後調用Close()、Dispose()；
查找坑出現的思路：
1、啟用連接池後，前後兩次查詢使用的連接都是同一個連接；
2、查詢完畢後，Connection調用Close()、Dispose()方法後並沒有真正關閉Session；
驗證過程：
首先看看oracle官方文檔對Connection Pool的解釋：
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 that
pool.
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 back
to 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 connections
available 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.
Connection調用Open()方法時，以下是oracle官方文檔描述：
The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established.
It is possible that the pool does not contain any unused connections when the Open() method is invoked. In this case, a new connection is established.
Connection調用Close()方法時，以下是oracle官方文檔描述：
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 be 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().
