A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

ORA-01000: Maximum Open Cursors Exceeded

1 min read
Scenario 1: The following message is received when exporting an Oracle Data Integrator (ODI) 12c Master Repository:

Scenario 2: While Running Collection Import Program. 

The Patch resulting from the Oracle SR fixed the problem.
Scenario 3: 
SQL> SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

HIGHEST_OPEN_CUR     MAX_OPEN_CUR
----------------                -----------------------
480                                 460
SQL> show parameter open_cursor

NAME          TYPE      VALUE
-----     ----------- ------------------------------
open_cursors  integer 460
Here we can see the highest_open_curosor is more than max_open_cursor. So to avoid this, increase the open_cursor values in spfile.
SQL> alter system set open_cursors=1000;

System altered
Issue got fixed !!  :)