Recently I received errors messages from OEM13c saying too many cursors were opened in a database:
instance_throughput:ORA-01000: maximum open cursors exceeded
My database had currently this open_cursors value:
SQL> show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 300
I decided to increase its value to 800:
SQL> alter system set open_cursors=800; System altered.
But a few minutes later I received again the same message. I decided to have a more precise look to discover what’s was happening.
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 300 800
So I need to find out which session is causing the error:
SQL> select a.value, s.username, 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 s.username is not null; VALUE USERNAME SID SERIAL# ---------- ------------------------------ ---------- ---------- 9 SYS 6 36943 1 SYS 108 31137 1 SYS 312 15397 300 SYS 417 31049 11 SYS 519 18527 7 SYS 619 48609 1 SYS 721 51139 0 PUBLIC 922 37 17 SYS 1024 1 14 SYS 1027 25319 1 SYS 1129 40925
A sys connection is using 300 cursors :=(( let’s see what it is:
Great the agent 13c is causing the problem :=((
I already encountered this kind of problem on another client’s site. In fact the agent 13c is using metrics to determine the High Availability Disk or Media Backup every 15 minutes and is using a lot of cursors. The best way is to disable those metrics to avoid ORA-01000 errors:
After reloading the agent and reevaluating the alert, the incident disappeared successfully :=)