ORACLE內SESSION

最大連線數：
Oracle 的最大連接數是在參數SESSIONS中,可以用
select value from v$parameter where name = 'sessions'
來查詢,但是需要DBA權限.


ORA-01000是異常中斷而沒有CLOSE,或者確實有這麼多的CUSOR同時在使用.
首先查DB的參數,
select value from v$parameter where name = 'open_cursors'
這個參數決定一個SESSION的最大打開CURSOR的個數,建議至少1000 .
然後,查
select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s
where user_name = 'username' and o.sid=s.sid group by o.sid, osuser, machine
order by num_curs desc;

裏面肯定有接近 'open_cursors' 的 session , 得到SID.

最後,查看是這些'open_cursors' 都是哪些SQL.

select q.sql_text from v$open_cursor o, v$sql q where q.hash_value=o.hash_value and o.sid = XXXXX;

現在你應該可以定位到那個頁面或MODULE導致這個的問題.

另關於這個問題,IBM官方解釋,以供參考:

http://www-1.ibm.com/support/docview.wss?uid=swg21218727

"ORA-01000: maximum open cursors exceeded" connecting to an Oracle database from WebSphere Application Server

Problem(Abstract)
Applications running in WebSphere Application Server V5 or V6 may see the following error when connecting to an Oracle database:

ORA-01000: maximum open cursors exceeded

Cause
The cause of the problem is that the maximum number of open cursors that are available in Oracle has been exceeded. In Oracle, the maximum number of open cursors is set per connection. The problem will occur if there are too many Statements and ResultSets open on a connection that was obtained from a WebSphere Application Server data source. This could occur if:

* The application fails to close Statement and ResultSet objects when it is finished using them
* The Statement cache size property for the data source is set to a value higher than the maximum number of open cursors in Oracle


Resolving the problem
To resolve the problem, you should first review the application code to ensure that it is closing all JDBC Statement and ResultSet objects when it finishes using them. Secondly, you should compare the value of the Statement cache size for your data source to the maximum number of open cursors in Oracle.

To check the Statement cache size:

* In WebSphere Application Server V5, navigate to the data source in the Admin Console. The Statement cache size appears on the main data source configuration panel.
* In WebSphere Application Server V6, navigate to the data source in the Admin Console. Under Additional Properties, select WebSphere Application Server data source properties. The first property listed on the resulting screen is the Statement cache size.


To check the maximum number of open cursors, open the initSID.ora file, where SID is the Oracle system identifier for the database. The file can be found in the /admin/SID/pfile directory. Look for the open_cursors setting.

Set the open_cursors in Oracle to a value larger than the Statement cache size for the data source in WebSphere Application Server. Also set the following property in the initSID.ora file:
cursor_sharing = force

These actions will ensure that the number of cursors opened by your application running in WebSphere Application Server will not exceed the maximum number of open cursors in Oracle.