Dump current open cursors when a ORA-01000 maximum open cursor error occurs

Question

Our backoffice sporadically suffers from ORA-01000 maximum open cursors error (The few time it happened was weekends when nobody work).

Is it possible to dump the details on open cursors in a table when such an error occurs so that we have time to analyse what causes it ?

This unfortunately has to be done on the Oracle side directly, as our application doesn't have access to the admin schema.

Answer 1

Yes, if you are a DBA you can create a system trigger on server error, and capture what you need:

CREATE TABLE mylogtable AS SELECT * FROM v_open_cursor WHERE 1=2;

CREATE TRIGGER TR_SERVERERROR AFTER SERVERERROR ON DATABASE
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF server_error(1) = 1000
  THEN
    INSERT INTO mylogtable
    SELECT *
      FROM v$open_cursor
     WHERE sid = SYS_CONTEXT('USERENV','SID');

    COMMIT;
  END IF;
END;

Or you can use monitoring software or write your own monitoring scripts to capture at intervals.

If you're not a DBA and they won't do the above for you, if you have the SELECT ANY DICTIONARY priv you can simply query v$open_cursor for your SID(s) at various times during your application run and you can probably see what's going on that way without using a trigger.

Make sure your application closes cursors that it opens. If open_cursors is extremely low, have you DBA increase it. But if we're talking thousands, there's an app design issue. No application should need thousands of open cursors.