PLSCOPE_SETTINGS in Oracle

Today i was working on an object creation script and had a trigger for audit columns like date inserted, date updated etc...

I constantly kept getting the error below and on further research found out a workaround from one of the blogs (thank you fellow blogger):


Error report:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
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
Failed to resolve object details 

Workaround: ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

By default the value is IDENTIFIERS:ALL 
The database default value is NONE and later found out it was SQL Developer setting.


After altering the session the trigger was compiled without any problems....hope this 
helps.

PL/SQL Compiler Setting in SQL Developer

Google is my friend :)

SYS_CONTEXT in Oracle

I know there is a functions to get user environment details in Oracle along with other valuable information for auditing purposes in your application. But i always tend to forget it, not anymore

SYS_CONTEXT (NAMESPACE, PARAMETER)       <<<< LOOK MA I AM PINK

Example: select sys_context('USERENV', 'HOST') from dual;

SET DEADLOCK_PRIORITY HIGH

Incase you are expecting a DEADLOCK in an environment where your process/job is going to run executing DML statements and you are fully aware that the process take priority over all other processes/jobs, you can set its priority to HIGH.

This specifies the rank/priority of your process higher than any other process when deadlock is detected.

Deadlock priority is set before the TRY block in the procedure.

Hope this helps....