Oracle 11g 異常 ORA-01000: maximum open cursors exceeded

今天發現資料庫發生

ORA-00604: error occurred at recursive SQL level 2

ORA-01000: maximum open cursors exceeded

查了一下, smon 用盡所有cursor , 所以從v$open_cursor 查了相對應的SQL_TEXT後，發現跟distributed transaction有關. local transaction 又連到remote dblink database 造成交易pending現象....所以smon嘗試rollback....

在SQL>執行以下job , 清掉, alert log 就正常了~

exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');

PS.
後來發現問題還是沒解決. 有可能是客戶的IBM data stage 在做同步時 沒有釋放cursor 導致.

Ref.

http://docs.oracle.com/cd/B14117_01/server.101/b10739/ds_txnman.htm 

Manually Resolving In-Doubt Transactions: Different Scenarios [ID 126069.1]
https://www-304.ibm.com/support/docview.wss?uid=swg21460967

http://dbakevlar.com/2011/06/
Master Note for Troubleshooting Oracle Managed Distributed Transactions [ID 100664.1]

How To Resolve Stranded DBA_2PC_PENDING Entries [ID 401302.1]