Re: Open cursor error

Can't answer why killing the top session helped fixing the issue. However I believe each SQL statements creates a cursor in memory and would be closed implicitly once it's done. I am not sure if it's kept open till commit happen.

 But as you mentioned the SQL statement or the session which was consuming 999 open cursor was coming on top was from Java but not any plsql procedure. So it may be something like opening the connection but not closing etc.

On Thu, 10 Aug, 2023, 12:50 am Pap, <oracle.developer35_at_gmail.com> wrote:

> 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-l