You Asked

Hi

I am getting following error while executing my batch job ORA-01000: maximum open cursors exceeded

Here is the flow of the batch job

1. Read file (xml).
2. Insert records into staging table using Bulk Insert.
2a. There is a trigger on Insert on this table which inserts the rows into History table.
3. On completion of bulk insert another batch job update the records into the staging table.

I would like to understand
1. when does the trigger gets fired. My understanding is that by default the trigger gets fired after the bulk insert is completed.
2. When there are 50000 rows inserted into the staging table through bulk insert does the trigger open a new cursor for each row?

Thanks
Ami

and Chris said...

1. That depends upon the trigger definition! These can be:

- before or after
- statement or row

So if your trigger is before statement, it only fires once, before Oracle inserts anything. If it's after each row, it fires once for each row (5,000 times in your example) after Oracle inserts the row.

Or you could have a compound trigger which combines the four options above into a single trigger:

https://oracle-base.com/articles/11g/trigger-enhancements-11gr1#compound_triggers

2. It depends upon how you've written your trigger! Each new statement is a new cursor. So the number of cursors opened depends on:

- Whether your trigger is row level or statement level
- What statements are in the trigger

The example below shows two different triggers.

1. A row level trigger that inserts into the audit table every time you insert a row.

2. A compound trigger that collects details of the rows you're inserting. It then inserts these to the audit table in a single bulk insert after the statement.

The row level trigger opens 100 cursors for the 100 audit inserts. The statement level trigger just 1 for the audit insert.

You can see this in the increase in cumulative opened cursors. Ex 1 goes from 84 -> 211 = 127 opened cursors. Ex 2 is 247 -> 255 = 8 opened cursors.

SQL> create table t (
  2    x int
  3  );

Table created.

SQL>
SQL> create table t_aud (
  2    x int,
  3    changed_by varchar2(30)
  4  );

Table created.

SQL>
SQL> create or replace trigger trig
  2  before insert on t
  3  for each row
  4  begin
  5    insert into t_aud
  6    values (:new.x, user);
  7  end;
  8  /

Trigger created.

SQL>
SQL> select n.name, s.value from v$statname n
  2  join   v$mystat s
  3  on     s.statistic# = n.statistic#
  4  where  name like 'open%cursor%';

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              84
opened cursors current                  4

SQL>
SQL> insert into t
  2    select rownum from dual
  3     connect by level <= 100;

100 rows created.

SQL>
SQL> select n.name, s.value from v$statname n
  2  join   v$mystat s
  3  on     s.statistic# = n.statistic#
  4  where  name like 'open%cursor%';

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative             211
opened cursors current                  4

SQL>
SQL>
SQL> create or replace trigger trig
  2  for insert on t compound trigger
  3    type typ is table of t%rowtype index by pls_integer;
  4     recs typ;
  5
  6    before each row is
  7     begin
  8       recs(recs.count+1).x := :new.x;
  9     end before each row;
 10
 11     after statement is
 12     begin
 13       forall i in 1 .. recs.count
 14         insert into t_aud
 15         values (recs(i).x, user);
 16     end after statement;
 17
 18  end;
 19  /

Trigger created.

SQL>
SQL> select n.name, s.value from v$statname n
  2  join   v$mystat s
  3  on     s.statistic# = n.statistic#
  4  where  name like 'open%cursor%';

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative             247
opened cursors current                  8

SQL>
SQL> insert into t
  2    select rownum from dual
  3     connect by level <= 100;

100 rows created.

SQL>
SQL> select n.name, s.value from v$statname n
  2  join   v$mystat s
  3  on     s.statistic# = n.statistic#
  4  where  name like 'open%cursor%';

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative             255
opened cursors current                  8

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Martin Rose, February 16, 2021 - 11:37 am UTC

Hi guys.

I’ve just come across “ORA-01000: maximum open cursors exceeded” in our app. too, but I’m mightly confused as to why, so I Googled the error which took me to this page.

But I’m none the wiser for having read thru it, and the above example confuses me.

With reference to the below trigger you wrote,

SQL> create or replace trigger trig
  2  before insert on t
  3  for each row
  4  begin
  5    insert into t_aud
  6    values (:new.x, user);
  7  end;
  8  /

Why are the cursors not simply closing themselves when the row-level trigger terminates each time? Surely one cursor is opened, then the same one is closed? I just don’t get how the cursors are stacking up here to contribute to the ‘too many open cursors’ error.

I’m asking because I’m beginning to wonder if this is where our error lies…

(Sadly, still running on V11.2).

February 17, 2021 - 4:25 pm UTC

Surely one cursor is opened, then the same one is closed?

Yes, they are.

I’m asking because I’m beginning to wonder if this is where our error lies…

First check your app for code that opens cursors but doesn't close them.

If you can't find any, it could be that open_cursors is set too low for your workload. Assuming the app closes cursors correctly, there's no overhead to setting this to a high value.

Either way, checking v$open_cursor will help you identify what your open cursors are

Martin Rose, February 18, 2021 - 4:13 pm UTC

> First check your app for code that opens cursors but doesn't close them.

I can't find any yet, though it is very complex. I will keep searching...

> If you can't find any, it could be that open_cursors is set too low for your workload. Assuming the app closes cursors correctly, there's no overhead to setting this to a high value.

But why would cursors stack up like this to breach the limit? There is only PL/SQL in use. I have done a scan for 'OPEN' and one for 'DBMS_SQL.OPEN_CURSOR' and examined the situations, but nothing appears awry. I don't see how 51 cursors can possibly be left open at once. (50 being our value for max_open_cursors).

If PL/SQL always manages implicitcursors correctly, and automatically closes explicitlyopened cursors (once out of scope of their definition), how is it even possible to breach the limit?

If all cursors are implicit, then surely the number open at any one moment in time is at most 1 ? (Unless they employ functions to return values into columns using separate SQL inside the functions, but even then it's only ever going be three, four, five or six).

With explicitly opened cursors, I appreciate they can become nested.

> Either way, checking v$open_cursor will help you identify what your open cursors are.

I'm logging,

  SELECT COUNT(*)
  INTO   No_of_Open_Cursors
  FROM   V$OPEN_CURSOR
  WHERE  CURSOR_TYPE = 'OPEN'
  AND    SID = sys_context('USERENV','SID');

There's 6 different cursor types returned,

PL/SQL CURSOR CACHED
SESSION CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED

Am I only interested in 'OPEN' ?

February 19, 2021 - 2:11 pm UTC

Every SQL statement has a cursor. If you have SQL inside a trigger, the parent statement is still active until the trigger completes. So you're going to have (at least) 2 open cursors:

- The triggering statement
- The SQL statement inside the trigger

There may also be various internal (OPEN-RECURSIVE) statements - those the database runs on your behalf to parse SQL, run background tasks such as auditing, etc.

You're interested all cursors with an OPEN type: OPEN, OPEN PLSQL, & OPEN-RECURSIVE.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Trigger error after Bulk insert 50000 rows :-ORA-01000: maximum open cursors exceeded

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs