ORA-01000
今天我也遇见了这个问题
有个概念一定要记牢，就是show parameter open_cursors这个参数是指每个session，包含递归的cursor能打开的最大游标数，
按照session 查看打开游标数，跟open_cursor对比
按用户查看打开游标的情况
按照SQL id打开游标排序
使用比例
session_cached_cursor是与ora 01000没有什么直接关系，出现ora 01000你就别想了
有个概念一定要记牢，就是show parameter open_cursors这个参数是指每个session，包含递归的cursor能打开的最大游标数，
按照session 查看打开游标数，跟open_cursor对比
点击(此处)折叠或打开
- SELECT SID, COUNT(*) FROM V$OPEN_CURSOR GROUP BY SID ORDER BY 2 DESC
按用户查看打开游标的情况
点击(此处)折叠或打开
- SELECT S.USERNAME, O.SID, O.CURSOR_CNT
- FROM (SELECT SID, COUNT(*) AS CURSOR_CNT FROM V$OPEN_CURSOR GROUP BY SID) O,
- V$SESSION S
- WHERE S.SID = O.SID
- AND S.USERNAME IS NOT NULL ORDER BY 1
点击(此处)折叠或打开
- SELECT O.*, S.USERNAME
- FROM (SELECT SQL_ID,
- SQL_TEXT,
- SID,
- COUNT(1) OVER(PARTITION BY SQL_ID) AS CURSOR_CNT
- FROM V$OPEN_CURSOR) O,
- V$SESSION S
- WHERE O.SID = S.SID
- AND USERNAME IS NOT NULL ORDER BY 4 DESC
点击(此处)折叠或打开
- SELECT 'session_cached_cursors' PARAMETER,
- LPAD(VALUE, 5) VALUE,
- DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
- FROM (SELECT MAX(S.VALUE) USED
- FROM V$STATNAME N, V$SESSTAT S
- WHERE N.NAME = 'session cursor cache count'
- AND S.STATISTIC# = N.STATISTIC#),
- (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
- UNION ALL
- SELECT 'open_cursors',
- LPAD(VALUE, 5),
- TO_CHAR(100 * USED / VALUE, '990') || '%'
- FROM (SELECT MAX(SUM(S.VALUE)) USED
- FROM V$STATNAME N, V$SESSTAT S
- WHERE N.NAME IN
- ('opened cursors current', 'session cursor cache count')
- AND S.STATISTIC# = N.STATISTIC#
- GROUP BY S.SID),
- (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors')
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
