Purging Data from Technical Tables
Introduction
Data volumes in Oracle Health Insurance applications typically grow when more and more data is processed.
This is not only true for operational data but also for technical tables
that support processing.
Examples of these technical tables are:
-
Tables for task processing
-
Tables for temporary enrollment and payment status data that is no longer needed
-
Batch job-related tables
Not all technical tables need purging: some clean-up automatically. Other tables may keep data during problem resolution (traceability) and statistics gathering however. An example of the latter category is enrollment data.
For this category, a purging process for the technical tables is available. This process must be triggered, it does not run automatically.
Enabling Data Purging for Technical Tables
The purging is implemented in the database as a set of PL/SQL packages. The next sections describe those packages. Those packages have to be executed by the owner of the database objects.
Package OHI_DATA_PURGE_PKG
The procedure calls both OHI_BATCH_PURGE_PKG and OHI_TASK_PURGE_PKG.
ohi_data_purge_pkg.purge_data
(p_purge_days_task in integer
,p_purge_days_batch in integer
);
Package OHI_BATCH_PURGE_PKG
The procedure purges data from tables related to batch job execution.
ohi_batch_purge_pkg.purge_data
(p_purge_days in integer);
Package OHI_TASK_PURGE_PKG
This procedure purges data from tables related to task execution.
ohi_task_purge_pkg.purge_data
(p_purge_days in integer);
Invoking the ohi_batch_purge_pkg.purge_data and ohi_batch_purge_pkg.purge_data packages individually does not guarantee successful execution.
For optimal results, it is recommended only to invoke the ohi_data_purge_pkg.purge_data .
|
Using DBMS_SCHEDULER for Frequently Purging Data
Oracle recommends frequent and automatic purging of technical data.
For example, through the use of a DBMS_SCHEDULER
job.
As an example, the following SQL script creates a job that executes the data purging routines every day at 04:00
and uses a retention period of 180 days:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_technical_data_daily',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
cla_data_purge_pkg.purge_data(p_purge_days => 180);
ohi_data_purge_pkg.purge_data(p_purge_days_task => 180,
p_purge_days_batch => 180
);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Purge OHI Claims Technical every day at 04:00');
END;
A CREATE JOB
privilege is essential for creating this script.
This is just an example. The retention times may need to comply with internal rules and regulations.
The |
Alternatively, Oracle Enterprise Manager can create and manage database jobs.
The benefits of cleaning data on a regular (for example, daily) basis ensure that the purge processes do not take a long time to complete. Note that the first-time execution may take a long time to complete.