Oracle DBA by Example. Code samples, Scripts, Reference.: ORA-01000: maximum open cursors by Example

=======================
General
=======================
Please see ORA-1000 errors, "Maximum open cursors exceeded." note

In case a session attempts to open more cursors than is set by the limit by open_cursors parameter, an error ORA-01000: maximum open cursors is thrown
In many cases, the cause is a bug in code, where open cursor is not being closed.

Good description:
https://www.javamadesoeasy.com/2015/12/solve-ora-01000-maximum-open-cursors.html

=======================
Resolution
=======================

Check Current status:

SELECT name, value FROM V$PARAMETER WHERE name = 'open_cursors';

NAME VALUE
------------- -------
open_cursors 400

Increase limit:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

=======================
Investigation
=======================

Get quick current status:

SELECT s.sid, a.value

FROM v$sesstat a,

v$statname b,

v$session s

WHERE a.statistic# = b.statistic#

AND s.sid=a.sid

AND b.name = 'opened cursors current'

AND a.value > 20

ORDER BY TO_NUMBER(a.value) DESC;

SID VALUE

---------- ----------

258 500

133 86

24 55

50 51

68 51

64 48

275 46

108 45

Get session details

COL OPEN_CURSORS for 999999
COL sid_serial for A20
COL username for A30
COL machine for A30
COL program for A20
COL spid for A10
COL sql_id for A20
set pagesize 1000
set linesize 120

SELECT V_SESSTAT.value OPEN_CURSORS,
V_SESSION.username,
V_SESSION.sid||'-'||V_SESSION.serial# sid_serial,
V_PROCESS.spid,
V_SESSION.machine,
V_SESSION.program,
V_SESSION.sql_id
FROM V$SESSTAT V_SESSTAT,
V$STATNAME V_STATNAME,
V$SESSION V_SESSION,
V$PROCESS V_PROCESS
WHERE V_SESSTAT.statistic# = V_STATNAME.statistic#
AND V_PROCESS.addr=V_SESSION.paddr
AND V_SESSTAT.sid = V_SESSION.sid
AND V_STATNAME.name = 'opened cursors current'
AND V_SESSTAT.value > 100;

OPEN_CURSORS USERNAME SID_SERIAL SPID MACHINE PROGRAM SQL_ID
------------ --------------- ---------- -------- ------------ -------------------- ---------------
192 GBR_EVERY_IPNQQ 981-57 17792 vrtsapuld12 JDBC Thin Client
121 GBR_EVERY_IPNQQ 387-231 21353 vrtsapuld13 JDBC Thin Client 5xshnmuuk55c1
123 GBR_EVERY_IPNQQ 773-489 21550 vrtsapuld14 JDBC Thin Client fz5q4cmx8y2qv
157 GBR_EVERY_IPNQQ 206-1209 18255 vrtsapuld12 JDBC Thin Client 5xshnmuuk55c1
176 GBR_EVERY_IPNQQ 1171-43 20442 vrtsapuld13 JDBC Thin Client gqq5y388ncpub
159 GBR_EVERY_IPNQQ 972-4059 21951 vrtsapuld14 JDBC Thin Client 5xshnmuuk55c1
260 GBR_EVERY_IPNQQ 35-44759 5528 vrtsapuld11 JDBC Thin Client cncfskhhd5w4u

Look into V$OPEN_CURSOR

SET LINESIZE 120

SET PAGESIZE 100

COL USER_NAME FOR A30

COL SQL_TEXT FOR A40

SELECT OPEN_CURSORS.sid,
OPEN_CURSORS.user_name,
OPEN_CURSORS.sql_text ,
COUNT(*)
FROM V$OPEN_CURSOR OPEN_CURSORS,
V$SESSION
WHERE V$SESSION.sid = OPEN_CURSORS.sid
AND V$SESSION.saddr = OPEN_CURSORS.saddr
GROUP BY OPEN_CURSORS.sid, OPEN_CURSORS.user_name, OPEN_CURSORS.sql_text

HAVING COUNT(*)> 100
ORDER BY count(*) DESC;

Get the sql text
COL sql_text FOR A100
SET linesize 120
SET pagesize 1000

SELECT sql_text FROM V$SQLAREA WHERE sql_id IN (
SELECT sql_id FROM (
SELECT V_SESSTAT.value,
V_SESSION.username,
V_SESSION.sid,
V_SESSION.serial#,
V_PROCESS.spid,
V_SESSION.machine,
V_SESSION.program,
V_SESSION.sql_id
FROM V$SESSTAT V_SESSTAT,
V$STATNAME V_STATNAME,
V$SESSION V_SESSION,
V$PROCESS V_PROCESS
WHERE V_SESSTAT.statistic# = V_STATNAME.statistic#
AND V_PROCESS.addr=V_SESSION.paddr
AND V_SESSTAT.sid = V_SESSION.sid
AND V_STATNAME.name = 'opened cursors current'

AND V_SESSTAT.value > 100

)

);

SQL_TEXT
----------------------------------------------------------------------------------------------------
MERGE INTO ipn_w_pnls wpnls USING (SELECT :1 plmn_id FROM DUAL) wpnls1 ON (wpnls.plmn_id = wpnls1.p
lmn_id) WHEN MATCHED THEN UPDATE SET network_list = :2 , pnl_series_version_id = :3 , pnl_series_id
= :4 , ts_last_modified = SYSDATE, PNL_CG_NAME = :5 , PNL_COMMUNITY_NAME = :6 ,PNL_INDEX_IN_SERIES =
:7 , PNL_COUNTRY_NETWORK_NAME = :8 WHEN NOT MATCHED THEN INSERT (plmn_id, network_list, pnl_series_
version_id, pnl_series_id, TS_LAST_MODIFIED, PNL_CG_NAME, PNL_COMMUNITY_NAME, PNL_INDEX_IN_SERIES ,P

NL_COUNTRY_NETWORK_NAME) VALUES (:9 , :10 , :11 , :12 ,SYSDATE, :13 , :14 , :15 , :16 )

SELECT V_SESSION.machine AS "SERVER",

V_SESSION.osuser AS OS_USER,

V_SESSION.program,

V_SESSION.module,

V_SESSION.sid,

V_SESSION.serial#,

V_PROCESS.spid,

V_SESSION.username,

V_PROCESS.program,

V_PROCESS.tracefile

FROM v$session V_SESSION,

v$process V_PROCESS

WHERE V_PROCESS.addr = V_SESSION.paddr

AND V_SESSION.type != 'BACKGROUND'

AND V_SESSION.sid = 586

AND V_SESSION.serial# = 62366

SERVER OS_USER PROGRAM MODULE SID SERIAL# SPID USERNAME PROGRAM TRACEFILE

-------------------- ---------- -------------------- -------------------- ----- ---------- ----- -------------- -------------------- --------------------------------------------------------------------------------

aus-vod-2-mng-1 iu JDBC Thin Client JDBC Thin Client 586 62366 2416 AUS_VODAF_IPNQ oracle@aus-vod-2-aps /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_2416.trc

Now open the trace file

/software/oracle/diag/rdbms/igt/igt/trace/igt_ora_2416.trc

Trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_2416.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name: Linux

Node name: aus-vod-2-aps-1

Release: 2.6.18-194.el5

Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010

Machine: x86_64

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 69

Unix process pid: 2416, image: oracle@aus-vod-2-aps-1

*** 2015-11-09 00:39:56.773

*** SESSION ID:(586.62366) 2015-11-09 00:39:56.773

*** CLIENT ID:() 2015-11-09 00:39:56.773

*** SERVICE NAME:(SYS$USERS) 2015-11-09 00:39:56.773

*** MODULE NAME:(JDBC Thin Client) 2015-11-09 00:39:56.773

*** ACTION NAME:() 2015-11-09 00:39:56.773

*********START PLSQL RUNTIME DUMP************

***Got internal error Exception caught in pl/sql run-time while running PLSQL***

***Got ORA-1000 while running PLSQL***

PACKAGE BODY AUS_VODAF_IPNQQ.START_ETL_PROCESS:

library unit=f8587390 line=475 opcode=107 static link=2ac5d4b50300 scope=1

FP=2ac5d4b50a78 PC=1194c90dc Page=0 AP=2ac5d4b50300 ST=2ac5d4b50cf8

DL0=2ac5d4ada5d0 GF=2ac5d4ada710 DL1=2ac5d4ada660 DPF=2ac5d4ada6f8 DS=1194c94c8

And finally open the code in question

PROCEDURE updatetapmanagementtable (

v_plmn_code IN VARCHAR,

v_state_code IN NUMBER,

v_is_error IN NUMBER

)

v_file_to_update varchar(100);

CURSOR file_name_to_update(v_plmn_code in varchar)

select

distinct TT.FILE_NAME file_name

from tap_temp tt,tap_management tm

where tt.plmn_code = v_plmn_code

and TT.FILE_NAME = TM.FILE_NAME;

BEGIN

setDBStreamTag;

OPEN file_name_to_update(v_plmn_code); --line 475

LOOP

FETCH file_name_to_update into v_file_to_update;

EXIT WHEN file_name_to_update%NOTFOUND;

UPDATE tap_management tm

SET tm.state = v_state_code,

tm.is_error_flag = v_is_error,

tm.time_stamp_last_updated = SYSDATE

WHERE tm.file_name = v_file_to_update;

END LOOP;

COMMIT;

removeDBStreamTag;

insert_log

('updatetapmanagementtable',

'updatetapmanagementtable - setTotalSubNetworkCommunity :: , nw: '

|| v_plmn_code

|| 'v_state_code: '

|| v_state_code

);

/* Free cursor used by the query. */

CLOSE file_name_to_update;

END;

In the code above there is no exception handling, which would cause cursor to be in open state, because CLOSE file_name_to_update is never reached.

=======================

Oracle Reference
=======================
Troubleshooting Open Cursor Issues

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Wednesday, November 11, 2015

ORA-01000: maximum open cursors by Example

No comments:

Post a Comment