Building an Automated Table Archival & Purge Utility for Oracle ATP

Every transactional table has an expiration date on its usefulness — most of the rows an application ever touches are recent, and everything older just sits there, quietly inflating storage costs and slowing down scans and backups. In an Autonomous Transaction Processing (ATP) database, that dead weight accumulates fast, and by the time it shows up on a bill or a query plan, it's already a problem.

We can build an Archival & Purge Utility to handle it before it gets that far: a PL/SQL engine, orchestrated by Oracle Integration Cloud (OIC), that exports aging records to OCI Object Storage, purges them from the source tables once the export is confirmed, and leaves operations teams with a clear, auditable trail of every run.

1. Overview

The utility is a config-driven pipeline that:

Exports "aging" data from tables defined in a control table to OCI Object Storage as Data Pump dump files (.dmp)
Purges the exported data from the source tables once the export is confirmed successful
Cleans up local scratch directories to reclaim database storage
Provides a restore path to re-import archived data back into the database on demand

It's split into two layers:

Database layer — a PL/SQL package (here referred to generically as ATP_ARCHIVE_UTIL_PKG) that does the heavy lifting: Data Pump exports/imports and OCI Object Storage transfers.
Orchestration layer — OIC integrations that trigger jobs, poll for completion, and send notification emails.

2. Architecture & Components

2.1 Database Layer — `ATP_ARCHIVE_UTIL_PKG`

The database is the execution engine for all data movement. Key pieces:

Component	Purpose
Config table (`atp_tbl_archive_config`)	Defines which tables to archive, the date column to filter on, retention period in months, and the target OCI bucket/path
Log table (`atp_tbl_archive_log`)	Audit trail for every export, import, and cleanup step — timestamps, row counts, statuses, and error messages
DBMS_DATAPUMP	Exports filtered data to `.dmp` files and imports them back
DBMS_CLOUD	Moves dump and log files between `DATA_PUMP_DIR` and OCI Object Storage
DBMS_SCHEDULER	Wraps long-running export/import work into background jobs (`submit_archive_job`, `submit_import_job`) so the calling integration doesn't time out

The config table looks like this:

Name                 Null?    Type
-------------------- -------- --------------
TABLE_NAME           NOT NULL VARCHAR2(128)
DATE_COLUMN                   VARCHAR2(128)
RETENTION_MONTHS              NUMBER
HAS_ATTACHMENTS               VARCHAR2(1)
IS_ACTIVE                     VARCHAR2(1)
TARGET_BUCKET                 VARCHAR2(200)
TARGET_PATH                   VARCHAR2(300)
PURGE_AFTER_UPLOAD            VARCHAR2(1)
NAMESPACE                     VARCHAR2(80)
OCI_BASE_URL                  VARCHAR2(200)

And the log table captures the full lifecycle of a run, including the date range that was actually archived:

Name                  Null?    Type
--------------------- -------- --------------
LOG_ID                NOT NULL NUMBER
OIC_RUN_ID            NOT NULL VARCHAR2(100)
TABLE_NAME                     VARCHAR2(128)
RUN_DATE                       DATE
OCI_BUCKET_NAMESPACE           VARCHAR2(50)
OCI_BUCKET_NAME                VARCHAR2(100)
OCI_FILE_NAME                  VARCHAR2(200)
FULL_OCI_PATH                  VARCHAR2(800)
ARCHIVE_STATUS                 VARCHAR2(100)
ROWS_PROCESSED                 NUMBER
FILE_URI                       VARCHAR2(1000)
PURGE_STATUS                   VARCHAR2(50)
ERROR_MESSAGE                  VARCHAR2(800)
CUTOFF_DATE                    TIMESTAMP(6)
CREATED_BY                     VARCHAR2(200)
OPERATION_TYPE                 VARCHAR2(80)
START_DATE                     TIMESTAMP(6)
END_DATE                       TIMESTAMP(6)

2.2 Orchestration Layer — OIC

Two integrations sit on top of the database layer:

Main orchestration integration — acts as the trigger. It calls submit_archive_job or submit_import_job, gets back a job name, and enters a polling loop calling check_job_status until the job reaches SUCCEEDED or FAILED. It also supports ad-hoc cleanup of the DATA_PUMP_DIR directory.
Notification integration — fires after a run completes. It calls get_log_html to pull a pre-formatted, color-coded HTML summary of the run and emails it to the operations/admin team.

2.3 Package Specification

CREATE OR REPLACE PACKAGE ATP_ARCHIVE_UTIL_PKG AS


    PROCEDURE cleanup_directory (
        p_prefix     IN VARCHAR2  DEFAULT NULL,
        p_oic_run_id IN VARCHAR2  DEFAULT 'ADHOC',
        p_log_run    IN VARCHAR2  DEFAULT 'N',
        p_created_by IN VARCHAR2  DEFAULT NULL
    );

    PROCEDURE export_data (
        p_query        IN CLOB,
        p_table        IN VARCHAR2,
        p_prefix       IN VARCHAR2,
        p_bucket_path  IN VARCHAR2,
        p_credential   IN VARCHAR2,
        p_oic_run_id   IN VARCHAR2,
        p_start_dt     IN TIMESTAMP  DEFAULT NULL,  -- min date of source data
        p_end_dt       IN TIMESTAMP  DEFAULT NULL,  -- cutoff / retention boundary
        p_created_by   IN VARCHAR2   DEFAULT NULL
    );

    PROCEDURE import_from_bucket (
        p_object_uri   IN VARCHAR2,
        p_credential   IN VARCHAR2,
        p_oic_run_id   IN VARCHAR2,
        p_prefix       IN VARCHAR2  DEFAULT NULL,
        p_src_schema   IN VARCHAR2  DEFAULT NULL,
        p_tgt_schema   IN VARCHAR2  DEFAULT NULL,
        p_src_table    IN VARCHAR2  DEFAULT NULL,
        p_tgt_table    IN VARCHAR2  DEFAULT NULL,
        p_created_by   IN VARCHAR2  DEFAULT NULL
    );

    PROCEDURE run_archive (
        p_oic_run_id  IN VARCHAR2,
        p_credential  IN VARCHAR2,
        p_table_name  IN VARCHAR2  DEFAULT NULL,
        p_created_by  IN VARCHAR2  DEFAULT NULL
    );

    PROCEDURE get_log_html (
        p_oic_run_id      IN  VARCHAR2,
        p_operation_type  IN  VARCHAR2  DEFAULT NULL,
        p_run_date        IN  VARCHAR2  DEFAULT NULL,
        p_email_body      OUT CLOB
    );

    PROCEDURE submit_archive_job (
        p_oic_run_id  IN  VARCHAR2,
        p_credential  IN  VARCHAR2,
        p_table_name  IN  VARCHAR2  DEFAULT NULL,
        p_created_by  IN  VARCHAR2  DEFAULT NULL,
        p_job_name    OUT VARCHAR2
    );

    PROCEDURE submit_import_job (
        p_object_uri  IN  VARCHAR2,
        p_credential  IN  VARCHAR2,
        p_oic_run_id  IN  VARCHAR2,
        p_prefix      IN  VARCHAR2  DEFAULT NULL,
        p_src_schema  IN  VARCHAR2  DEFAULT NULL,
        p_tgt_schema  IN  VARCHAR2  DEFAULT NULL,
        p_src_table   IN  VARCHAR2  DEFAULT NULL,
        p_tgt_table   IN  VARCHAR2  DEFAULT NULL,
        p_created_by  IN  VARCHAR2  DEFAULT NULL,
        p_job_name    OUT VARCHAR2
    );

    PROCEDURE check_job_status (
        p_job_name  IN  VARCHAR2,
        p_status    OUT VARCHAR2,
        p_detail    OUT VARCHAR2
    );

END ATP_ARCHIVE_UTIL_PKG;

Note the p_start_dt / p_end_dt additions on export_data — these capture the actual date range that was archived in each run (the minimum date found in the source data through the retention cutoff), which turned out to be one of the most-requested additions to the audit trail once the utility went into production. Operators wanted to see which window of data a given file represented, not just when the job ran.

3. Core Workflows

3.1 Archival & Purge

OIC triggers the archival process for all active tables in the config table (or a single table, for targeted runs).
The package reads atp_tbl_archive_config to determine the retention cutoff date for each table.
Data older than the cutoff is copied into a temporary table (<TABLE_NAME>_ARCH_TMP).
DBMS_DATAPUMP exports the temporary table to DATA_PUMP_DIR.
DBMS_CLOUD.PUT_OBJECT pushes the .dmp and .log files to the target OCI bucket, organized by YYYYMMDD/OIC_RUN_ID.
If purge_after_upload = 'Y' and the upload succeeded, the source table is purged of the archived records.
The temporary table and local directory files are cleaned up.

A deliberate safety property here: the purge only happens after a successful upload is confirmed. If the export or upload fails, the run is logged as EXPORT_FAILED and the purge step is skipped entirely — nothing gets deleted from the source table unless the export to cold storage is verified.

3.2 Import / Restore

OIC triggers an import, providing the OCI object URI, credential, and optional target table/schema overrides.
DBMS_CLOUD.GET_OBJECT pulls the .dmp file back into DATA_PUMP_DIR.
DBMS_DATAPUMP restores the table, supporting schema and table remaps for restoring into a different location than the original.
Local directory files are cleaned up on completion.

4. Operational Runbook Highlights

Onboarding a new table

Archival for a table is entirely config-driven — no code changes needed:

INSERT INTO atp_tbl_archive_config (
    table_name, date_column, retention_months, target_bucket,
    target_path, purge_after_upload, namespace, oci_base_url, is_active
) VALUES (
    'ORDER_HISTORY', 'CREATED_DATE', 12, 'my-archive-bucket',
    '/orders/', 'Y', 'my-oci-namespace',
    'https://objectstorage.us-ashburn-1.oraclecloud.com', 'Y'
);
COMMIT;

Setting purge_after_upload to 'N' exports the data for cold storage but leaves it in place — useful for a dry run before enabling full purge on a new table.

Running a manual archive

DECLARE
    v_job_name VARCHAR2(100);
BEGIN
    ATP_ARCHIVE_UTIL_PKG.submit_archive_job(
        p_oic_run_id => 'MANUAL_RUN_001',
        p_credential => 'MY_OCI_CRED',
        p_table_name => 'ORDER_HISTORY',  -- NULL runs all active config tables
        p_created_by => 'ADMIN',
        p_job_name   => v_job_name
    );
    DBMS_OUTPUT.PUT_LINE('Job Submitted: ' || v_job_name);
END;
/

Restoring archived data

DECLARE
    v_job_name VARCHAR2(100);
BEGIN
    ATP_ARCHIVE_UTIL_PKG.submit_import_job(
        p_object_uri => 'https://objectstorage.../o/20260518/RUN_123/exp_order_history_20260518_100000_01.dmp',
        p_credential => 'MY_OCI_CRED',
        p_oic_run_id => 'RESTORE_RUN_001',
        p_tgt_table  => 'ORDER_HISTORY_RESTORED',  -- import to a new table to avoid overwrites
        p_job_name   => v_job_name
    );
END;
/

Handling stranded files after a crash

If the database crashes mid-export/import, dump files can be left behind in DATA_PUMP_DIR, quietly eating storage:

BEGIN
    ATP_ARCHIVE_UTIL_PKG.cleanup_directory(
        p_prefix     => NULL,  -- NULL clears all .dmp and .log files; use a prefix to scope it
        p_oic_run_id => 'MANUAL_CLEANUP',
        p_log_run    => 'Y'
    );
END;
/

Common error codes

Error	Cause & fix
`ORA-31626: job does not exist`	Data Pump failed to start — verify `DATA_PUMP_DIR` exists and the user has tablespace quota/privileges
`cannot parse object URI`	The object URI doesn't match the standard OCI format (`/n/{namespace}/b/{bucket}/o/{object}`)
`purge_status = EXPORT_FAILED`	Data was copied to the temp table, but the Data Pump export or OCI upload failed — purge is automatically skipped to prevent data loss. Check credential validity and bucket permissions

5. Reporting

Every run — export, import, or cleanup — produces a color-coded HTML summary via get_log_html, showing total files processed, completed/error/no-data counts, and a per-table breakdown with the actual date range archived, row counts, and status. This gets emailed automatically to the operations team after each run, so nobody has to go spelunking in the log table unless something actually needs investigating.

Takeaways

A few design choices made this utility easy to operate and trust in production:

Config over code — onboarding a new table is an INSERT, not a deployment.
Fail-safe purging — data is never deleted until its export to cold storage is confirmed.
Full audit trail — every step, including the exact date range archived, is logged.
Async by design — DBMS_SCHEDULER jobs let the orchestration layer poll instead of blocking on long-running Data Pump operations.
Human-readable observability — automatic HTML run summaries mean failures get noticed immediately instead of during a quarterly audit.

Automated Table Archival & Purge Utility for Oracle ATP