Auto Purge
Introduction
Data volumes in Oracle Health Insurance applications typically grow when more data is processed. Oracle Health Insurance application has different PL/SQL packages for different purging processes, to name a few: purging data from technical tables, purging operational data, Log data purge, and Datafile purge; these processes do not run automatically. Instead, a user must configure the purge jobs to run on regular schedules during the planned maintenance window. So, the Oracle Health Insurance application has introduced an auto purge job to prevent manual maintenance of scheduler jobs.
Package OHI_AUTO_PURGE_PKG
This procedure invokes all the purging processes from the following tables:
ohi_auto_purge_pkg.purge_all;
Field | Description |
---|---|
Package Name |
Name of the PLSQL package for a specific purging process. |
Procedure Name |
Name of the PLSQL procedure which implements the specific purging process. |
Description |
Description of the purging process: Tells about what exactly the procedure purges. |
Retention Days |
The number of days the data will be retained, after that, it will be purged. |
Minimum Days |
The minimum number of days data must be retained. (i.e., always retention days >= minimum days). |
Maximum Days |
The maximum number of days data can be retained (i.e., always retention days <= maximum days). |
Enabled |
The flag used to include/exclude a specific purging package from the auto purge process. |
Remarks |
Any remarks specific to the purging process (for example, a brief explanation for why a certain retention days is set). |
The following table lists all the purge jobs that will be part of auto purge package:
Purge job | Default Retention Days | Enabled? | Description |
---|---|---|---|
OHI_ACT_PURGE_PKG.PURGE_ACT |
60 |
Y |
This package is used to purge activities. |
OHI_FIN_PURGE_PKG.PURGE_ALL |
60 |
N |
This package is used to purge financial data. |
OHI_REPL_PURGE_PKG.PURGE_DATA |
60 |
Y |
This package is used to purge replication data. |
OHI_DATA_PURGE_PKG.PURGE_DATA |
60 |
Y |
This package is used to purge tasks and batch data. |
OHI_OIC_PURGE_PKG.PURGE_INT_OUTBOUND_MESSAGES |
60 |
Y |
This package is used to purge outbound messages. |
OHI_OIC_PURGE_PKG.PURGE_ASSIGNED_AGG_AND_AGGREGATE_CHANGE_EVENTS |
60 |
Y |
This package is used to purge aggregate change events. |
OHI_DFP_PURGE_PKG.PURGE_DATAIMPORT_DATA |
60 |
Y |
This package is used to purge import data. |
OHI_PURGE_DATAFILESET_PKG.PURGE_DATA |
60 |
N |
This package is used to purge datafile sets. |
OHI_LOG_PURGE_PKG.PURGE_PHI_LOGS |
2560 |
Y |
This package is used to purge PHI logs. |
OHI_LOG_PURGE_PKG.PURGE_DYLO_LOGS |
30 |
Y |
This package is used to purge dynamic logic logs. |
OHI_LOG_PURGE_PKG.PURGE_APPLICATION_LOGS |
30 |
Y |
This package is used to purge application logs. |
Read and Update Auto Purge Metadata
Use the HTTP API resource generic/autopurgemetadata
to manage retention periods for various purge processes, such as datafileset purge, exchange purge, log purge, etc.
-
To get an overview of specific purging processes, execute a
GET
request togeneric/autopurgemetadata/{id}
. -
To get an overview of all available purging processes, perform a search operation on
generic/autopurgemetadata/search
. -
To update the following for a specific purging process, execute a
PUT/PATCH
request togeneric/autopurgemetadata/{id}
:-
retentionDays
-
enabled
-
remarks
-
For example, to get a list of all available purging processes, POST
the following JSON payload on generic/autopurgemetadata/search
:
{ "resource": { "q": "" } }
For example, to set a retention period of 90 days for purging application logs. Patch
the following JSON payload on generic/autopurgemetadata/{id}
:
{ "retentionDays": 90, "enabled": true, "remarks": "Increasing the amount from 60 to 90 days because of business requirements" }
|
Use DBMS_SCHEDULER for Frequent Auto Purge
Oracle recommends frequent auto-purge of technical data through a DBMS_SCHEDULER
job.
For example, the following SQL script creates a job that executes the auto-purge every day at 04:00.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'auto_purge_daily',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
ohi_auto_purge_pkg.purge_all;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Executes Auto-purge every day at 04:00');
END;
A CREATE JOB
privilege is essential for creating this script.
Logging
The execution time of each procedure is logged in the ohi$install_log
table.
A select query on the table can retrieve the execution time for each procedure.
To obtain execution time for an activity purge is demonstrated in the following example:
select log_date , log_action , log_text from ohi$install_log where log_action = 'OHI_ACT_PURGE_PKG.PURGE_ACT' ;
Log Date | Log Action | Log Text |
---|---|---|
05-OCT-12 04.05.47.026881000 PM |
OHI_ACT_PURGE_PKG.PURGE_ACT |
execution time is 0.149 s |
The following table is used to store specific information related to the purging job:
Column Name | Description |
---|---|
Log_date |
To store the timestamp at which the entries are logged. |
Log_action |
It is used to store information on the executed purge job. |
Log_text |
It is used to store the execution time (in seconds) of the corresponding purge job. |