Shrink of MQ tables causes system stop

Document ID : KB000084598
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
N/A

When a system stops and you see “Shrink of MQ tables causes system stop” it is not the actual cause of the error. 
 
Shrinking of the MQ tables is not the root cause of the system stop rather it is a “SELECT FOR UPDATE SKIP LOCKED” on the MQ tables.
 
Example:
 
SELECT MQWP.*,ROWID
FROM MQWP
WHERE MQWP_SchedTime<=:A0001
ORDER BY MQWP_Priority, MQWP_SchedTime, MQWP_PK FOR UPDATE SKIP LOCKED;
 
The shrink was blocked by a select and this led to other sessions querying the MQ table also being locked.
 
In order to resolve the issue, install the attached procedure.  Upon testing the procedure, remove it from UC_REORG_NEW to UC_REORG.
 
NOTEContact Automic Support before installing the procedure to ensure that the attached file is not outdated.
Cause:
Cause type:
By design
Root Cause: “SELECT FOR UPDATE SKIP LOCKED” causes the system to stop but the “Shrink of MQ tables causes system stop” message follows that error and is displayed. The actual cause is due to the MQ table being locked.
Resolution:
1) DBA should kill the locking session.
2) Disable UC_REORG and do the reorganization manually.

Fix Status: No Fix

Fix Version(s):
N/A
Additional Information:
Workaround :
N/A
File Attachments:
UC_Reorg_new.sql