【案例49】ORA-01000:超出打开游标的最大数

问题现象

在登录系统时提示报错：ORA-01000 超出打开游标的最大数。

问题分析

游标就是看成是指向结果集的指针。可以把它看成一种资源，或者一种数据结构。 

ORA-01000是开发中常见的异常。这个异常表示程序中打开的游标数目> 数据库中设定的可以打开的最大游标数。
 
这个异常通常由2个原因导致
 
1）、 数据库中设置的maximum open cursors太小
 
2）、在 java jdbc程序中通常是代码中打开的resultset 或 preparedstatment 没有被关闭。 

首先排查数据库设置的游标参数open_cursors，此值为3000，说明已经之前扩展过相关参数，默认为300。已经比较大了。

show parameter open_cursors

查询油标谁占用的最多发现为exxk_xxx_dzd相关表。（表名已经隐藏）

select count(*),sql_text from v$open_cursor group by sql_text order by 1 desc;  

查询游标的详细信息，发现占用游标的机器为测试环境。 

select o.sid,
       osuser,
       machine,
       o.sql_id,
       o.sql_text,
       o.cursor_type,
       count(*) num_curs
  from v$open_cursor o, v$session s
 where user_name = 'xxx' ---数据库用户
   and o.sid = s.sid
 group by o.sid, osuser, machine, o.sql_id, o.sql_text, o.cursor_type
 order by num_curs desc;

解决方案

扩展游标数为5000，重启NC，无需重启数据库。

alter system set open_cursors=5000 SCOPE=BOTH; 

由于游标数已经设置3000多，算是比较大，所以需要与相关业务研发沟通，相关代码中打开的resultset 或 preparedstatment 是不是没有被关闭，需要出具相关补丁进行修复。

代码修复逻辑

通常这个异常是由Java程序未关闭ResultSet 或者 PreparedStatment 造成的。正确的适用方法， 总是把close()放在finally中，这样不管有没有发生其他异常，都会关闭ResultSet 和 PreparedStatement。在代码层面更换执行方法为executeBatch()，但是在进行大批量数据库操作时，要进行分批执行

Statement stmt = conn.createStatement();
 
try {
    ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
    try {
        while ( rs.next() ) {
            System.out.println( "Name: " + rs.getString("FULL_NAME") );
        }
    } finally {
        try { rs.close(); } catch (Exception ignore) { }
    }
} finally {
    try { stmt.close(); } catch (Exception ignore) { }
}

 相关示例

//示例，获取直接用不了

public class XXX  implements XXXX {

	@Override
	public void AAA (String tablename, String[] lockkeys, String pk_user) throws TMDBLockException {
		try
		{
			//1：查询各个lockkeys是否已经在数据库中，存入Map 
			B pm = B.getInstance(C.getInstance().getUserDataSource());
			IUAPQueryBS query = NCLocator.getInstance().lookup(IUAPQueryBS.class);
//			BaseDAO dao = new BaseDAO();
			String strSql = ""; 
			List<Object[]> rsList = new ArrayList<Object[]>();
			ArrayListProcessor processor = new ArrayListProcessor(); 
			strSql = "select a.lockkey,b.user_name from " + tablename + " a left join sm_user b on a.userid = b.cuserid  where a.lockkey in ('' ";
			for(int i=0;i<lockkeys.length;i++){
				strSql = strSql + "," + "'" + lockkeys[i] + "'";
			}
			strSql = strSql + ") "; 
			Map<String,String> lockmap = new HashMap<String,String>();
			String key = "";
			String value = "";
//			rsList= (List<Object[]>)pm.D().executeQuery(strSql, processor);
			rsList= (List<Object[]>)query.executeQuery(strSql, processor);
			if (rsList.size()!=0 ){
				int lenrow = rsList.size(); 
				for (int i=0;i<lenrow;i++){
					Object[] objs = (Object[])rsList.get(i);
					if (null !=objs[0]){
						key = objs[0].toString();
						if (null !=objs[1]){
							value = objs[1].toString();
						}else{
							value = "";
						} 
						lockmap.put(key, value);
					}					
				}
			}
			//2：如果lockkeys已经存在，Update；如果lockkeys不存在，Insert
			for (int i = 0; i < lockkeys.length; i++) {
				key = lockkeys[i];
				StringBuffer sql = new StringBuffer();
				if(null ==lockmap.get(key)){  
					sql.append("insert into ").append(tablename).append("(").append("lockkey,userid").append(") values ('").append(lockkeys[i]).append("','").append(pk_user).append("')");
					pm.D().addBatch(sql.toString());
				}
			}
			pm.D().executeBatch();
		} catch (DbException e) {
			nc.bs.logging.Logger.error(e.getMessage(), getClass(), "DBLockManageServiceImpl.AAA");	
			throw new nc.vo.tmpub.exception.E(e.getMessage(), e);
		} catch (BusinessException e) {
			nc.bs.logging.Logger.error(e.getMessage(), getClass(), "出现异常");	
			throw new nc.vo.tmpub.exception.E(e.getMessage(), e);
		}


	}
}