These scripts can be used to maintain the size of the User Message tables in an Oracle database, following these instructions:
Before implementing the archive scripts please ensure a complete and working backup of your database has been taken.
Then, in SQL*Plus Worksheet:-
1. Connect to the appropriate User for your ITBM database.
2. Run message-tables.ora to create 2 archive tables - tps_user_message_archive, tps_user_message_recipient_arc.
3. Run rogue-entries.ora to create procedure sp_user_message_rogue_entries.
4. exec sp_user_message_rogue_entries;
This deletes any incorrect entries from the tps_user_message table where there is no entry in the tps_user_message_recipient table.
5. Run MMArchive.ora to create procedure sp_MMArchive.
6. exec sp_MMArchive;
This will archive all sent messages.
You should now check that most of the entries in the tps_user_message and tps_user_message_recipient tables have been moved to the tps_user_message_archive and tps_user_message_recipient_arc tables
Finally, schedule the stored procedure SP_MMArchive to run once each day after the last backup of the day.
Note: The SQL version of this can be found here: http://community.landesk.com/support/docs/DOC-4949
From 731 onwards you can use a scheduled bulk action to clear the entries in the message recipient table instead. More details on how to do this can be found within the 73NewFeatures guide under Clearing the Message Recipient table within the Changes in 731 section.