How to Resolve "ORA-06000 Deadlock Error" Messages

Document ID : KB000050128
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

If the system.log or database alert log contains an ORA-06000 deadlock error for the NH_ALARM_OCCURENCE table, or the Live Exceptions History scheduled job fails due to this ORA error, one possible solution is to recreate the BITMAP indexes as b-tree indexes.

Solution:

If the system.log or database alert log contains an ORA-06000 deadlock error for the NH_ALARM_OCCURENCE table, or the Live Exceptions History scheduled job fails due to this ORA error, one possible solution is to recreate the BITMAP indexes as b-tree indexes.

To make this change, this please run the following commands from a command prompt in the $NH_HOME/bin/sys folder.

nhisql "drop index NH_ALARM_OCCURRENCE_IX3"nhisql "drop index NH_ALARM_OCCURRENCE_IX4"

nhisql "create index NH_ALARM_OCCURRENCE_IX3 on NH_ALARM_OCCURRENCE (CLEAR_CODE)"nhisql "create index NH_ALARM_OCCURRENCE_IX4 on NH_ALARM_OCCURRENCE (ALARM_RULE_ID)"

Next, run the following query to check if the database now has NORMAL b-tree indexes. Watch your typing, as it will be easy to make mistakes with the double quotes, single quotes, and pipe symbols.

nhisql "select a.INDEX_NAME||';'||b.INDEX_TYPE||';'||a.COLUMN_NAME||';'||a.COLUMN_POSITION||';' from user_ind_columns a, user_indexes b wherea.INDEX_NAME = b.INDEX_NAME and b.TABLE_NAME 'NH_ALARM_OCCURRENCE';"

Check the second column. All values should be set to NORMAL now, type BITMAP should not be there.

No restart of the database is necessary after completing these steps.