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

Linux: Get a tree-view of your directories

To my surprise there is one thing Windows has, that Linux lacks: A graphical tree-view of a given directory-structure. On Windows it’s as simple as typing “tree”. But as always on Linux, one could grab the necessary building-blocks and combine them to get what we need.

On http://www.centerkey.com/tree/ I found this nice one-liner based on “ls”,”grep” and “sed”:

ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/   /' -e 's/-/|/' 

Example:

[apache]$ ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/   /' -e 's/-/|/'
   .
   |-descriptor
   |-doc
   |-io
   |---export
   |---import
   |-languages
   |-skins
   |-specials
   |---SMWCheckInstallation
   |-tools
   |---maintenance
   |-----export
   |-----resources
   |-------dd_templates
   |---onto2mwxml
   |---smwadmin

There is another approach here, which I didn’t checked.