ORA-21780: Maximum number of object durations exceeded

Question

There are 20 tables (table_id from 1 to 20) whose count needs to be taken and updated in DATA_COUNT.REC_CNT_ACTUAL(tablename.coumnname).

For 13 tables the count update is successful. Then this error pops up.

Initially I wrote the below as a procedure by passing table_id and table_name. The procedure was called continously 20 times. I though that might be the reason for error: ORA-21780: Maximum number of object durations exceeded.

Then I removed that procedure . Now these 20 update statements are in 20 different places across the package. Still same error. Please help me understand the issue or suggest an alternative.

UPDATE DATA_COUNT
SET REC_CNT_ACTUAL=
  (SELECT COUNT(1) FROM TITLE
  )
WHERE TABLE_ID =20;

Below is the procedure that was used:


PROCEDURE DELETE_COUNT(
    PI_TABLE_ID   IN NUMBER,
    pi_table_name IN VARCHAR2)
AS
  pragma autonomous_transaction;
  V_SQLERRM    VARCHAR2(4000) := NULL;
  V_SQLERRCODE VARCHAR2(50)   := NULL;
  V_CNT        NUMBER;
  V_SQL        varchar2(4000);

BEGIN
  V_SQL:='select COUNT(1) from '|| PI_TABLE_NAME;
  EXECUTE immediate V_SQL INTO v_cnt;
  UPDATE DATA_COUNT
  SET REC_CNT_ACTUAL=v_cnt
  where TABLE_ID    =PI_TABLE_ID;

  commit;

EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  v_sqlerrm    := SUBSTR(sqlerrm,1,255);
  v_sqlerrcode := SQLCODE;
  /*Log error*/
  datareduction_run_log (-20001, 'procedure DELETE_COUNT', CURRENT_TIMESTAMP, 'FAILED', 'Error in procedure DELETE_COUNT. Error Number:'||v_sqlerrcode||'-Error Msg:'||v_sqlerrm||'- backtrace - ' || dbms_utility.format_error_backtrace, SYSDATE, USER, NULL, NULL );
  RAISE_APPLICATION_ERROR(   -20001,'Unexpected Error: Error Number:'||V_SQLERRCODE||'-Error Msg:'||V_SQLERRM||'- backtrace - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END DELETE_COUNT;

This typically happens if there is infinite recursion in the PL/SQL function that is being executed. Can you post you full code — XING, Oct 31 '19 at 12:46
@Divya Sam, please can you show us the function: "Initially I wrote the below as a function by passing table_id and table_name." ALSO what do you want to say with this: "Then I removed that function now these 20 statements are in 20 different places across the package" ? There are 20 different functions now? Thanks! — VBoka, Oct 31 '19 at 12:47
@VBokšić no they are now written as simple update statements across a procedure at different places. So I assumed it will not get called again and again at same time. So recursive error may go away. It still persists though — Divya Sam, Oct 31 '19 at 12:50
So this select "SELECT COUNT(1) FROM TITLE" works fine ? And title is a table name right? — VBoka, Oct 31 '19 at 12:54
function added. yes 'TITLE' is just table name. No the simple update also is throwing error (it updates count of 13 tables properly. then throws this error) — Divya Sam, Oct 31 '19 at 12:55
Ok, for this 7 tables, please check if this SELECT count is working ok — VBoka, Oct 31 '19 at 13:00
Is there an `ON UPDATE`-trigger on the `DATA_COUNT` table? If so, maybe the trigger is causing the problem. — Erich Kitzmueller, Oct 31 '19 at 13:05
@ErichKitzmueller No . Data_Count is a temporary table created for this package. I have not written triggers for it. — Divya Sam, Oct 31 '19 at 13:07
So, this update is working for 13 tables and then on the 14th table `UPDATE DATA_COUNT SET REC_CNT_ACTUAL= (SELECT COUNT(1) FROM TABLE14 ) WHERE TABLE_ID =14;` it breaks ? Even when you just run this UPDATE yourself? Not through the procedure! — VBoka, Oct 31 '19 at 13:18
@vboksic I have written 14 UPDATE statements inside main procedure. I have deleted the child procedure DELET_COUNT. In this case as well breaks at 14th UPDATE. — Divya Sam, Oct 31 '19 at 13:30

score 2 · Answer 1 · answered Nov 03 '19 at 11:44

ORA-00018: maximum number of sessions exceeded

This error we get when we don't have enough sessions left out for our DDL/DML operations.

This is because a DDL/DML statement requires use of recursive DML and some recursive (data dictionary) operations in Oracle are done using a recursive session context!

Oracle will silently allocate a new session state object from session state object array (V$SESSION / X$KSUSE) and all the recursive operations state objects (locks, transaction state objects etc) will belong to that recursive session. Also data dictionary QUERIES (populating dictionary cache) are done using a separate recursive session.

No. of session available and sessions parameter value a can be checked using:

SQL> select value from v$parameter where name = 'sessions';

VALUE
--------------------------------------------------------------
170

SQL>
SQL> select count(*) from v$session;

  COUNT(*)
----------
       163

Now you see that even though we have 7 left overs session but still we are getting error ORA-00018.

So let's double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:

 SQL> select * from v$resource_limit where resource_name = 'sessions';

   RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
sessions                                       170             170        170        170

There you will find the CURRENT_UTILIZATION will be same the session parameter value set.

Also RECURSIVE session is used for recursive data dictionary calls and V$SESSION doesn't show these.

You can see such session by querying table "

SQL> select paddr from v$session where sid = userenv('sid');

PADDR
----------------
BF46591C

In another session I ran this query, to see which sessions belong under that process state object identified above:

SQL> select 
    decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna
    from x$ksuse s where ksusepro = 'BF46591C';

DECODE(BITAND(KSUSEFLG,19),17,   KSUUDSNA
-------------------------------- ------------------------------
USER                             SYSTEM
RECURSIVE                        SYS

This two might be the reasons in your case as well.

So if you hit the ORA-00018 error, then make your sessions parameter array larger or configure your application to use less connections or sessions.

Note: You need DBA privilege to query these tables.

Divya Sam · Answer 2 · 2019-11-18T07:10:57.560

0

This is how I solved this particular issue:

The issue was same procedure was UPDATING(DML) same table(object) DATA_COUNT several times. It updated successfully 26 times. But after that the Update(DML) started to fail. I re-wrote few Updates in another procedure. And it worked.

edited Nov 18 '19 at 07:10

answered Nov 05 '19 at 13:27

Divya Sam

131
2
10

ORA-21780: Maximum number of object durations exceeded

2 Answers2