DEV Community

Cover image for Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler
mrcaption49
mrcaption49

Posted on

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

#opensource #data #datascience #database

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

  • The procedure begins by reading from the configuration table CMN_DROP_PARTITION, which defines which partitioned tables need cleanup, how many partitions should be retained, and which partitions must be excluded.
  • For each active table entry, it retrieves the table owner, table name, and retention count. Next, it queries the Oracle data dictionary views (dba_tab_partitions) to fetch all available partitions for that table in ascending order by position. Once the partitions are listed, it checks the configured exclusion partition so that it is never dropped, even if it falls outside the retention period.
  • It then calculates how many partitions must be kept, ensuring the most recent ones (based on partition position) remain untouched. The procedure identifies the older partitions that are beyond the retention limit and marks them as candidates for dropping. Using dynamic SQL, it generates and executes ALTER TABLE DROP PARTITION statements for each of these outdated partitions.
  • Error handling is usually included to skip over issues like locked partitions or dependencies. Finally, this process ensures partition cleanup is automated, data-driven, and consistent, improving database performance and storage efficiency without manual intervention.
  • The solution automates partition maintenance in Oracle by combining a PL/SQL procedure, a configuration table, and a scheduled job. The procedure CMN_DROP_PARTITIONS_PR dynamically identifies and drops partitions older than the retention rules defined in CMN_DROP_PARTITION.
  • The configuration table controls how many partitions to keep, which partitions to exclude, and which tables are active for cleanup, making the logic fully data-driven. A DBMS Scheduler job then executes the procedure every Monday at 3 AM, ensuring cleanup happens consistently without manual effort.
  • Together, these components deliver a reliable, automated, and flexible way to keep partitioned tables lean and manageable.
  • Managing partitioned tables efficiently is crucial in large-scale databases, especially when dealing with high-volume transactional or logging data.
  • If older partitions are not purged regularly, the database grows uncontrollably, impacting performance, storage, and maintenance overhead.
  • To address this challenge, we implemented an automated partition cleanup mechanism in Oracle. The solution involves three components working together:
  • (1) a PL/SQL procedure that drops old partitions beyond a configurable retention period,
  • (2) a configuration table that defines retention rules and exceptions per table, and
  • (3) a DBMS Scheduler job that triggers the cleanup procedure at regular intervals without manual intervention. This approach ensures that database housekeeping is automated, safe, and consistent, reducing operational effort while keeping only the required data available.

1. The Cleanup Procedure —

  • At the core of this solution is the PL/SQL procedure CMN_DROP_PARTITIONS_PR.
  • It scans all partitioned tables configured for cleanup, checks how many partitions should be retained, and automatically issues ALTER TABLE DROP PARTITION commands for older partitions.
  • Any errors are logged but don’t interrupt the overall execution, ensuring that one bad partition doesn’t stop the cleanup process. 
CREATE OR REPLACE PROCEDURE CMN_OWNR.CMN_DROP_PARTITIONS_PR IS
    v_Dropstr_Sql varchar2(4000);
BEGIN
    FOR i IN (
        SELECT * FROM (
            SELECT u.table_name,
                   partition_name,
                   partition_position,
                   RANK() OVER(PARTITION BY u.table_name ORDER BY u.partition_position DESC) rnk,
                   cdp.no_of_part_to_retn retn_prt_days
            FROM user_tab_partitions u, cmn_drop_partition cdp
            WHERE cdp.table_name = u.table_name
              AND cdp.exclude_partition != u.partition_name
              AND cdp.active = 'Y')
        WHERE rnk > retn_prt_days
        ORDER BY rnk
    ) LOOP
        BEGIN
            v_Dropstr_Sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_Dropstr_Sql;
            v_Dropstr_Sql := '';
        EXCEPTION WHEN OTHERS THEN
            ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR',i.table_name||':'||i.partition_name,NULL,SQLERRM,NULL,NULL);
        END;
    END LOOP;

ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Drop Partition',NULL,'CMN PARTITIONS DROP JOB GOT COMPLETED SUCCESSFULLY AT:'||systimestamp,NULL,NULL);
EXCEPTION WHEN OTHERS THEN
    ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Main EXCP',NULL,SQLERRM,NULL,NULL);
END CMN_DROP_PARTITIONS_PR;
/
Enter fullscreen mode Exit fullscreen mode

2. Configuration Table —

  • The cleanup rules are defined in the CMN_DROP_PARTITION table. For each table, we can specify how many partitions to retain, which partitions to exclude from deletion, and whether the rule is active.
  • This makes the procedure flexible and data-driven. 
CREATE TABLE CMN_DROP_PARTITION (
  table_name         VARCHAR2(100),
  exclude_partition  VARCHAR2(100),
  table_owner        VARCHAR2(15),
  no_of_part_to_retn NUMBER(5),
  active             VARCHAR2(1),
  created_date       TIMESTAMP(6),
  created_by         VARCHAR2(30),
  modified_date      TIMESTAMP(6),
  modified_by        VARCHAR2(30)
);


-- Sample entries
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_UPLD_DATA', 'P_PREDEC24','Y', 'CMN_OWNR',31, SYSDATE, 'ACCELYA-IT');
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_LOG_REQ_RESP', 'P_PRE24','Y', 'CMN_OWNR',7, SYSDATE, 'ACCELYA-IT');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

In this example:

  • For CMN_UPLD_DATA, the last 31 partitions are retained, but partition P_PREDEC24 is excluded from deletion.
  • For CMN_LOG_REQ_RESP, the last 7 partitions are retained, excluding P_PRE24.

3. Scheduling with DBMS Scheduler —

  • Finally, the procedure is scheduled using Oracle’s DBMS Scheduler.
  • We created a job that runs the cleanup process every Monday at 3 AM, ensuring weekly housekeeping without manual effort. 
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CMN_DROP_PARTITION_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'cmn_drop_partitions_pr;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=03; BYMINUTE=00; BYSECOND=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB To drop older partition from CMN tables');
END;
/
Enter fullscreen mode Exit fullscreen mode
  • This job guarantees the cleanup procedure runs at a fixed schedule, keeping the database lean and partitions under control.

Summary —

With this implementation, we achieved a robust and automated partition cleanup strategy. The procedure ensures that only the latest required partitions are kept, the configuration table provides flexibility and control without code changes, and the DBMS Scheduler job automates execution at off-peak hours. Together, these three components create a reliable maintenance framework that improves performance, reduces storage costs, and minimizes manual intervention in partition management.

Top comments (0)