Semi-annual W005_PROCESS_LOG archiving

This task designed to be run semi-annually to prevent table W005_PROCESS_LOG from being overflowed and throwing of out of space error(OracleException (0x80004005): ORA-01652: unable to extend temp segment by 128 in tablespace TEMP)

The core of the process sits in the Stored Procedure CAMIS.SP_BACKUP_W005_PROCESS_LOG

The purpose of the procedure is to:

1. Rename (ALTER) the current W005_PROCESS_LOG table into the archived versions using patterns such as
   

   

2. Cascade changes such triggers replacements, indexes, synonyms accordingly

3. Send email upon completion using parameters from the table DA004_SETTING

   1. Field TO: is taken from “wa005_process_log table half-yearly backup” => DLOTTAFCCCDBASUPPORT@tc.gc.ca;

   2. Field FROM: “ERROR_MAIL_FROM“ => DLOTTAFCCCDBASUPPORT@tc.gc.ca;

   3. Body of the email constructed accordingly:

      1. Success message with timestamp

      2. Error message with the body of the error and timestamp

4. The Stored procedure is using another store procedure - CAMIS_DUI.sp_email_notify(…) to send emails