Script:Diagnostic ORA-01000 maximum open cursors exceeded

最新推荐文章于 2025-04-16 17:05:03 发布

转载最新推荐文章于 2025-04-16 17:05:03 发布 · 62 阅读

本文提供了一组SQL脚本，用于诊断Oracle数据库中出现的ORA-01000错误——打开游标过多。通过这些脚本可以检查游标的使用情况、缓存命中率、硬解析比例等关键指标。

以下脚本可以用于诊断ORA-01000打开游标过多错误:

set linesize 140 pagesize 1400

select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/

select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' 
order by 1 
/

select sid, count(*) from v$open_cursor group by sid
order by 2 
/

Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 

exec dbms_lock.sleep(300);

Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 


@?/rdbms/admin/awrrpt
upload the awr report

or 
select dbms_workload_repository.awr_report_text(l_dbid     => dbid,
                                                l_inst_num => instance_number,
                                                l_bid      => mid - 1,
                                                l_eid      => mid)
  from (select vd.dbid, vi.instance_number, mid
          from v$database vd,
               v$instance vi,
               (select max(snap_id) mid from dba_hist_snapshot dhs))
/