Oracle Server Session Terminated by fatal error (ORA-00603)
-- When creating or recompiling a database trigger, we get 
-- ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated error along  
-- with ORA-00600, ORA-00603 and ORA-00604 errors as shown in the diagram below.

ORA-00600, ORA-00603 and ORA-00604 error

-- This can be simply be resolved by executing the command shown below, resetting
-- the PLSCOPE_SETTINGS to NONE.  
-- By setting the value to ALL will result in PL/Scope collecting data for all
-- identifiers in the PL/SQL source program, such as identifiers in package bodies.

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

-- Query to know PL/Scope Identifier Data in 'STANDARD' and 'DBMS_STANDARD'
-- This can be customized to know other owner's PL/Scope Identifier Data.

SELECT UNIQUE object_name
FROM  all_identifiers
WHERE  object_name IN ('STANDARD', 'DBMS_STANDARD')  
AND   owner='SYS'
;

-- Query to know PL/Scope Identifier Data in user objects

SELECT UNIQUE name, object_name, usage
FROM  all_identifiers
WHERE  owner= user;
 

-- In Oracle SQL Developer, it can also be done in the GUI by menu option

-- Tools -> 
    Preferences -> 
      Database ->
        PL/SQL Compiler ->
          Change the PLScope identifiers from All to None -> Click OK

Reference

[1] Oracle Database Advanced Application Developer's Guide 11g Release 1 (11.1) B28424-03

[2] Audit Tracking Trigger

[3] Auto ID Creation Trigger

[4] Some Oracle Errors and Resolution

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 27th, 2014

  38246