Open cursor error
Date: Thu, 10 Aug 2023 00:49:45 +0530
Message-ID: <CAEjw_fgk_ELP24aJy_uJOsXBCo1AgJcHH_DqL1gHCoyo++SY8Q_at_mail.gmail.com>
Hello Experts,
We have this database with Oracle version 19C.We encounter below error Ora-01000 for one of the application jobs even though we have not changed anything in the code recently. Then as this parameter "open_cursors" description says "max # cursors per session" and we have set it as ~1000. We thought , by just rerunning the job may make the job succeed , as because this limit was for the specific SESSION and killing and rerunning the job might grab a new session and thus will make the job succeed. But rerunning again made the job fail with the same error.
ORA-01000: maximum open cursors exceeded
Then we ran the below query on "GV$*" views and found one of the top sessions was showing the 'opened cursors current' VALUE as ~999 and other followed sessions were having values in the range of ~300. And that specific top session was showing as "inactive" though in v$session but the sql it was showing from a different job altogether. But still we killed that session. And then reran the job which was failing with that Ora-01000 and surprisingly that succeeded.
So I have a few questions here,
1)As this parameter "open_cursors" is a limit for a specific session but
not at the database level, So how come killing an existing session makes
the new session/job successful?
2)Though we have the query which the top session was pointing to, that was
inactive, and was directly submitted from Java code and also that code was
not changed recently. So how can that query cause Ora-01000? Or Is there
any other way to debug and find more info for the root cause and fix for
the Ora-1000 error from the AWR/dba_hist* views? Want to ensure this error
should not appear again in future.
SELECT a.VALUE,
s.username,
s.sid,
s.serial#
FROM gv$sesstat a, gv$statname b, gv$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
AND b.inst_id = s.inst_id
AND s.inst_id = 1
AND s.inst_id = b.inst_id
order by a.value desc
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 09 2023 - 21:19:45 CEST