Re: Open cursor error

Thank you.

I was tring to see how the "opened cursors" count is increasing for the session, so i tried running the simple statement as below by connecting to a sql plus session.

Create table test (c1 varchar2(4000));

Insert into test values('AB');
Insert into test values('CD');
Insert into test values('EF');
Insert into test values('XX');
Insert into test values('YY');

> 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