In my last post I outlined a simple application logging table and API for recording log messages. If enabled in a busy system then the size of the table will quickly grow. I noted that the table is partitioned for easy maintenance. In this post I provide a simple package for managing those maintenance tasks.
Purging the LOGS table of old data is clearly the key maintenance task. Given the table is partitioned by date the simplest solution is to drop partitions older than whatever the retention period should be. The other maintenance task, although a relatively minor one, is to rename the system generated partitions with meaningful names.
So, without further ado, I present PKG_LOG_MAINT, which exposes just two routines:
- update_partition_names – a procedure that renames any system generated partition name to LOGS_yyyymmdd, where yyyymmdd represents the date that the data pertains to
- clear_logs – a procedure that drops old table partitions. The procedure accepts a single optional parameter and will pruge any partition where the data is older than that date. If a date is not provided then data older than the default retention as specified by the package body constant c_default_retention_days is used.
PKG_LOG_MAINT has dependencies on PKG_LOG, PKG_ERR and PKG_DDL. PKG_DDL performs the actual DDL required and PKG_ERR is used as the mechanism to log any errors associated with the DDL that might arise.
Mark Hoxey 