Antoine_Sauteron_1266

Maintaining MQ tables in Oracle

Discussion created by Antoine_Sauteron_1266 on Jan 26, 2017
Latest reply on Apr 18, 2017 by Rick_Murray_7767
Our DBAs made a check on the size of the Oracle table spaces associated to  some AutomationEngine servers.
    Most of the time, the size of the table related to the MQMEM table is quiet small (300 to 500MB).
    But some servers have a huge one (14 and 25 GB).

   How to know if this is really in use ? Is there a way to purge these tables (COLD restart ?) and if we do this what is the impact of the database size (does the COLD restart free the MQMEM tables space, and do we get the space back ?)
COLD start will indeed perform a TRUNCATE on all MQ tables, however it's usually not recommended to resort to this solution. Please refer to this discussion for more details: https://community.automic.com/discussion/7218/the-cold-start-hot-topic

I would suggest doing this:

1 - Ensure that reorganization of the MQ tables is enabled on the affected Automation Engine. Look for 'MQ_CHECK_TIME' in UC_SYSTEM_SETTINGS and verify that it is not set to '0', in which case MQ tables are not automatically maintained by the AE.

2 - Eventually replace your current version of the UC_REORG procedure with the enhanced version available here : https://automic.force.com/support/apex/CommunityArticleDetail?id=ka3b0000000g05b                                           
Please align with your DBA for this.

3 - The space used by tables needs to be reclaimed. This has to be done by the DBA as well. Here is an Oracle knowledge link that should give you guidance on how to proceed : https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#ADMIN01401

Kind regards,
Antoine

Outcomes