You need to modified the parameter with following command:ĪLTER SYSTEM SET open_cursors = 500 SCOPE=BOTH Ĥ. IF Highest value is near by max value then or you got error in alert log. Show parameter open_cursors #default value is 300.ģ. Select max(a.value) as highest_open_cur, p.value as max_open_curįrom v$sesstat a, v$statname b, v$parameter p Check the value of cursor: highest open and max value Where a.statistic# = b.statistic# and s.sid=a.sidġ. Select a.value, s.username, s.sid, s.serial#įrom v$sesstat a, v$statname b, v$session s IF you want to check which session is utilizing maximum cursor: Session cached cursors is used to set the value of cached cursor in memory for better performance. Open Cursor parameter is used to set the maximum value of a session can open the cursor. If value of Open Cursor is set to 300 then only one session can open maximum 300 cursor at a time when it try to exceed this value then it will generate ORA-01000 error. Open Cursor is the cursor maximum value used by a session in oracle. Hope this will give some idea on resolving the cursor issue. It was informed informed to the Application team to go for further investigation of the code. Once we Identified the defective Query "ab58yscqkptra" In next 5 mins It crossed the earlier Limit 2300Ģ401 AUTOLOS_ETL_USER ab58yscqkptra 2711 43134 S.username=’AUTOLOS_ETL_USER’ order by 1 SQL> / VALUE USERNAME SQL_ID SID SERIAL#ġ0 AUTOLOS_ETL_USER az2uqz8kug7wy 2648 1181ġ0 AUTOLOS_ETL_USER az2uqz8kug7wy 3095 41991ġ0 AUTOLOS_ETL_USER az2uqz8kug7wy 2809 52001ĩ6 AUTOLOS_ETL_USER ab58yscqkptra 2711 43134 SQL> select a.value, s.username, s.sql_id, s.sid, s.serial# 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 Here the DB username was AUTOLOS_ETL_USER It will be more helpful If we know the DB username who has kicked off the JOB. Select a.value, s.username, s.sql_id, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#Īnd s.sid=a.sid and b.name = ‘opened cursors current’ and s.username is not null order by 1 Once the Job started we found few Queries which were reaching near to the open_cursor limit.
Once we incresed the open_cursor value we asked them to re-run there job again to monitor which is the defective Query on the code causing this. alter system set open_cursor=2500 scope=both sid=’*’ system altered. Step to increase the open_cusor Limit: 1.SQL> show parameter open_cursor NAME TYPE VALUE So from the DBA prospective since it was a critical job we had to increase the open_cursor value for the immediate fix, but we had to identify the root cause to provide a permanent solution. To keep a defective session from filling up the library cache or clogging the CPU with millions of parse requests, the OPEN_CURSORS database parameter must be set toĪpps team complained one of there job is getting failed. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache. This private SQL area is called context area or cursor. Oracle PL/SQL allows you to name this area. Let’s try to understand what really this cursor is !!!!!!Īny application that uses Oracle Database as back end repository, Oracle Identity Manager runs several SQL statements.įor every SQL statement execution in Oracle Database, certain area in the memory is allocated. We need to identify what exactly causing this. They will immediately point to DBA to increase the open_cursors value, but that’s not the solution always. Sometime in the real world the application jobs while running may face the error “ORA-01000: maximum open cursors exceeded”.