DBMS_SCHEDULER.DEFINE_CHAIN_STEP Results in ORA-600 [17059] With Recursive Usage Of Select Count(*) From Dual (Doc ID 1277102.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

You are encountering ORA-600 [17059] on 11.2.0.1 or 11.2.0.2 using DBMS_SCHEDULER.DEFINE_CHAIN_STEP to build chain_steps.

The Current SQL is:
select count(*) from dual

The PL/SQL stack shows DBMS_SCHEDULER and DBMS_ISCHED and DBMS_RULEADM_INTERNAL:

----- PL/SQL Call Stack -----
object line object
handle number name
000000022DABA7D0 165 package body SYS.DBMS_RULEADM_INTERNAL
000000022DADC128 1754 package body SYS.DBMS_ISCHED
000000022DAE3618 1638 package body SYS.DBMS_SCHEDULER
000000021A29C848 37 anonymous block

Call stack is similar to:
kgeriv_int, kgeriv, kgesiv, kgesic3, kglhdgc, kglLock, kglget,
kxsGetLookupLock, kksfbc, kkspfda

Querying v$sql_shared_cursor shows child number 1 - 32767 got Y in column(T):
TYPECHECK_MISMATCH - The existing child cursor is not fully optimized.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms