ORA-01000: maximum open cursors exceeded

喝醉酒的小白

已于 2022-03-25 14:00:51 修改

阅读量5.4k

点赞数 3

分类专栏： DBA 文章标签：数据库

于 2022-03-21 22:40:07 首次发布

本文链接：https://blog.csdn.net/hezuijiudexiaobai/article/details/123648308

版权

DBA 专栏收录该内容

702 篇文章 15 订阅

订阅专栏

本文介绍了数据库游标的概念及其在数据检索中的作用，包括声明、打开、使用和关闭游标的步骤。当遇到ORA-01000错误时，提出了多种诊断和解决策略，如检查open_cursors参数、分析游标使用情况、查找问题SQL及用户、监控等待事件等，旨在优化游标管理和减少资源消耗。

摘要由CSDN通过智能技术生成

问题

在这里插入图片描述

游标

SQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行（零行到多行）。简单地使用SELECT语句，没有办法得到第一行、下一行或前10行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

使用游标涉及几个明确的步骤。

❑ 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句和游标选项。

❑ 一旦声明，就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。

❑ 对于填有数据的游标，根据需要取出（检索）各行。

❑ 在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的DBMS）。
问题
数据库技术知识库 > ORA-01000：超出打开游标的最大数 > image2021-12-29_18-7-30.png

思路一

1 检查open_cursor参数和当前cursor

show parameter open_cursors

select count(*）from v$open_cursor;

select count(*）from gv$open_cursor;

每个会话可以打开的最大游标数，也就是SQL语句数。推荐将该参数设置为1500。

alter system set open_cursors=1500 scope=spfile sid='*';

2 查找子游标数量

Prompt Top 10 hight version:
select rownum rn,t.*
from (select inst_id,
             sql_id,
             version_count,
             round(sum (sharable_mem)/1024/1024,4) mem_mb
      from gv$sqlarea 
      group by inst_id,sql_id,version_count
      order by version_count desc,inst_id,sql_id) t
where rownum<=10;

Prompt
Prompt Top 10 from sharable_mem:
select rownum rn,t.*
    from (select inst_id,
                 sql_id,
                 version_count,
                 round(sum (sharable_mem)/1024/1024,4) mem_mb
          from gv$sqlarea
          group by inst_id,sql_id,version_count
          order by mem_mb desc,inst_id,sql_id) t
    where rownum<=10;

3 查看哪个用户打开的游标多

select s.USERNAME,S.sid,s.SERIAL#,p.SPID, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s,v$process p
where /*user_name = '' and*/ o.sid=s.sid  and p.ADDR=s.PADDR
group by s.USERNAME,S.sid,s.SERIAL#,p.SPID, osuser, machine
having count(*) > 2
order by num_curs;

4 查看最后这个sid会话，执行的SQL

 select q.sql_id,count(*)
 from v$open_cursor o, v$sql q
 where q.hash_value=o.hash_value and o.sid = &sid
 group by q.sql_id order by 2;

5 查看sql_id的数量

select SQL_ID,count(*) from v$open_cursor group by sql_id having count(*) >4 order by 2;

6 查看sql_txt是否一样

select sql_fulltext from v$sqlarea where sql_id='&sql_id';

思路二

1 查看等待事件

select event,count(*) from gv$session where wait_class<>'Idle' group by event order by 2;

2 查看预警日志

cdump 

grep -i ora- alert*.log

3 用户和游标数

select  USER_NAME, count(*) as "OPEN CURSORS"  from v$open_cursor where sid in (select s.sid  from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by user_name;

4 查看mos

在这里插入图片描述

ORA-1000 When Running JDBC Application (Doc ID 1077199.6)

5 初步判断客户程序SQL没有关闭游标

请客户协调业务人员排查是否打开cursor后没有正常关闭。

思路三

1 每个会话的游标数

select sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME 
from v$open_cursor 
where sid in (select s.sid  
	from v$sesstat a, v$statname b, v$session s 
	where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) 
	group by sid ,sql_text,USER_NAME order by 3;

2 每个sql语句当前已打开和解析或缓存的游标数

select substr(b.sql_text, 0,59),count(*) from v$open_cursor b group by b.sql_text order by 2;

3 很多SQL语句没有绑定变量

set linesize 200 pages 99
col SUBSTR(SQ.SQL_TEXT,0,60) for a65
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999 
WITH c AS
 (SELECT PARSING_SCHEMA_NAME,FORCE_MATCHING_SIGNATURE, COUNT(*) cnt
    FROM v$sqlarea
   WHERE FORCE_MATCHING_SIGNATURE != 0
   GROUP BY FORCE_MATCHING_SIGNATURE,PARSING_SCHEMA_NAME
  HAVING COUNT(*) > 20),
sq AS
 (SELECT  sql_text,
         FORCE_MATCHING_SIGNATURE,
         row_number() over(partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
    FROM v$sqlarea s
   WHERE FORCE_MATCHING_SIGNATURE IN
         (SELECT FORCE_MATCHING_SIGNATURE FROM c))
SELECT c.PARSING_SCHEMA_NAME,substr(sq.sql_text,0,60), sq.FORCE_MATCHING_SIGNATURE, c.cnt "unshared count"
  FROM c, sq
 WHERE sq.FORCE_MATCHING_SIGNATURE = c.FORCE_MATCHING_SIGNATURE
   AND sq.p = 1
 ORDER BY c.cnt ;

思路四

1 每个sql语句当前已打开和解析或缓存的游标数

select substr(b.sql_text, 0,59),count(*) from v$open_cursor b group by b.sql_text order by 2;

2 查看占用最多游标sql语句的执行频率

select sql_id, sql_text from v$sql where sql_txt like '&sql_txt';

结论：这条select 语句占用的游标比较多，执行频率也较高，但是没有绑定变量，这边请协调研发人员排查一下，应该就可以解决这个游标问题。

思路五

找出导致错误的语句

select a.value, s.username, s.sid, s.serial#,s.program,s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;

select sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in (select s.sid  from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by sid ,sql_text,USER_NAME;

找出导致错误的程序

select s.program,count(*)  from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null group by s.program order by 2;

显示哪些查询导致打开游标最大化，请运行以下 SQL 语句：显示打开最大游标但没有正常关闭后续游标的顶部查询

select  USER_NAME, count(*) as "OPEN CURSORS"  from v$open_cursor where sid in (select s.sid  from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by user_name;