oracle能否避免使用游标,Oracle游标使用达到最多限制报错

java的一个调度程序，发现运行一段时间之后会报出如下错误。

javax.mail.MessagingException: IOException while sending message;

nested exception is:

java.io.FileNotFoundException: =/dac_adb/disp/user_down/=dac_adb_1631.tar.gz (No such file or directory)

at com.sun.mail.smtp.SMTPTransport.sendMessage(SMTPTransport.java:577)

at com.deboo.common.MailSend.send(MailSend.java:138)

at com.deboo.common.MailSend.execute(MailSend.java:151)

at com.deboo.app.adb.common.TaskRuntimeRunnable.sendMail(TaskRuntimeRunnable.java:123)

at com.deboo.app.adb.common.TaskRuntimeRunnable.run(TaskRuntimeRunnable.java:82)

at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Thread.java:534)

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)

at com.deboo.app.adb.common.TaskRuntimeMain.dealHisTask(TaskRuntimeMain.java:145)

at com.deboo.app.adb.common.TaskRuntimeMain.main(TaskRuntimeMain.java:595)

问题排查：

查看数据库的游标使用情况：

SQL> desc v$open_cursor

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

SADDR                                              RAW(8)

SID                                                NUMBER

USER_NAME                                          VARCHAR2(30)

ADDRESS                                            RAW(8)

HASH_VALUE                                         NUMBER

SQL_ID                                             VARCHAR2(13)

SQL_TEXT                                           VARCHAR2(60)

select * from v$open_cursor where user_name='D'

发现SID为1976的用户使用了1816 rows selected.也就是使用了全部2000个的游标的绝大多数。

update tb_dac_adb_req t set t.req_status='0' where sysdate >这个SQL语句占了绝大多数。

这样问题定位就非常简单准确了。