How to Resolve "ORA-06000 Deadlock Error" Messages

Products

• CA eHealth

Issue/Introduction

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.

Environment

Release:
Component: EHORA