Oracle: ORA-00600 on creating trigger on 11.2

Here’s a quick note about a recent problem:

Developer tried to run a db-script on an Oracle Database 11.2.0.2 but got stuck every time at this really unspectacular “CREATE TRIGGER”

CREATE OR REPLACE TRIGGER "TR_TB_ELEMENT" 
BEFORE INSERT ON "TB_ELEMENT"
FOR EACH ROW BEGIN
  IF INSERTING THEN
    IF :NEW."EL_ID" IS NULL THEN
      SELECT SEQ_TB_ELEMENT.NEXTVAL INTO :NEW."EL_ID" FROM DUAL;
    END IF;
  END IF;
END;

with this error-message:

ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg1], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be created

At the blog of Dale Ward, who once had the same problem, I found as a workaround to disable PL/Scope. This can be done at session-level with this:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

After this was done, my trigger was created and compiled with no errors. To re-enable the PL/Scope set this:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'; 

A commentator at Dale’s blog noticed, that it was possible to create a trigger with TOAD while using SQL Developer (like me) raised the above error. So I verified this hint using SQL-Plus and, lo and behold, the trigger compiled with no error!

Further investigations revealed that my DB is running with the (default) init-parameter “plscope_settings=IDENTIFIERS:NONE”, while my SQL-Developer (v3.0) started every session with the default-setting “plscope_settings=IDENTIFIERS:ALL” (see menu EXTRAS>PREFERENCES>DATABASE>PL/SQL-COMPILER).

References in Metalink/MOS:
Bug 10174125
Bug 10094416
ID 1341014.1 (“Bug 10094416 is fixed in 12.1 release.”)

Advertisements

7 Responses to Oracle: ORA-00600 on creating trigger on 11.2

  1. vini says:

    Thank you !! saved many hours of debugging

  2. Dave says:

    Thanks, had no clue about this error. Previous SQL Developer settings never had the identifiers enabled.

  3. Pingback: Confluence: Volkswagen InfoNet

  4. Ricardo Jorge says:

    Great tip! I was having a hard time discovering what the problem was…

  5. No-reply says:

    Thank you very much!!

  6. John says:

    Thanks for the tip; I have no idea how you found it but it’s a lifesaver.

  7. SDLowry says:

    Thanks a million. This solved our problem and saved us a ton of time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: