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');
Then running the stats from gv$sesstat doesnt show a increase in the
'opened cursors current' after each Insert/DML statement irrespective of
COMMIT. Which means its not true that each DML in a session caused a "open
cursor" for that session.
So it seems this stats "opened cursors current" depends on the normal
explicit CURSOR declaration in which we have to open and then close the
cursor explicitly in plsql block/procedure. But then I am wondering how its
caused the query submitted directly from Java to have those 1000+ "open
cursor"?
Regards
Yudhi
On Fri, 11 Aug, 2023, 1:18 am Lok P, <loknath.73@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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
