DEV Community

Pranav Bakare
Pranav Bakare

Posted on

DBMS_SCHEDULER with Practical example | mrcaption49

#database #data #datascience #dataengineering

DBMS_SCHEDULER with Practical example complete overview

Every month, on the 28th day, Oracle’s DBMS_SCHEDULER automatically triggers the job named ADD_MSG_MON_PARTITION_JOB, which is defined to execute the stored procedure PIE_OWNR.ADD_NEXT_MONTH_PARTITION. When this job starts, it begins by calculating the first day of the next month based on the current system date (e.g., if today is July 28th, it calculates August 1st). Using this date, the procedure generates a partition name like P_AUG25 and constructs a dynamic ALTER TABLE SQL statement to add a new partition to the PIE_MSG_MONITOR table. The newly created partition will have a VALUES LESS THAN boundary set to the 1st of the month after next (e.g., September 1st), effectively covering all dates in August. This SQL is then executed immediately within the PL/SQL block. If the partition already exists (for example, if the job is rerun), the procedure captures Oracle error -14701 and silently skips creation to avoid failure. The job is defined to repeat monthly, ensuring a new partition is created in advance, just before the current month's data period ends. This proactive mechanism ensures that incoming data always has an appropriate partition to go into, thereby avoiding runtime errors and reducing manual DBA effort in managing monthly partitions.

PROCEDURE PIE_OWNR.ADD_NEXT_MONTH_PARTITION 

CREATE OR REPLACE PROCEDURE PIE_OWNR.ADD_NEXT_MONTH_PARTITION AS
  v_next_partition_date DATE := TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM'); -- 1st of next month
  v_partition_name      VARCHAR2(30);
BEGIN
  v_partition_name := 'P_' || TO_CHAR(v_next_partition_date, 'MONYY');

  EXECUTE IMMEDIATE '
    ALTER TABLE PIE_OWNR.PIE_MSG_MONITOR ADD PARTITION ' || v_partition_name || '
    VALUES LESS THAN (TIMESTAMP''' || TO_CHAR(ADD_MONTHS(v_next_partition_date, 1), 'YYYY-MM-DD') || ' 00:00:00'')
    TABLESPACE NGCSDAT02
    PCTFREE 10 INITRANS 1 MAXTRANS 255
    STORAGE (
      INITIAL 64K
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
    )';
EXCEPTION
  WHEN OTHERS THEN
    -- Optional: Log the error or ignore if partition already exists
    IF SQLCODE = -14701 THEN
      NULL; -- Partition already exists
    ELSE
      RAISE;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

DBMS_SCHEDULER.CREATE_JOB 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'ADD_MSG_MON_PARTITION_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PIE_OWNR.ADD_NEXT_MONTH_PARTITION',
    start_date      => TRUNC(SYSDATE, 'MM') + 27, -- runs on 28th of current month
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=28',
    enabled         => TRUE,
    comments        => 'Adds next month''s partition to PIE_MSG_MONITOR'
  );
END;
/
Enter fullscreen mode Exit fullscreen mode

🔍 Overview: What This Is and Why We Use It

This implementation automates the task of creating the next month's partition in an Oracle partitioned table named PIE_MSG_MONITOR. Each month, a new partition must be added to this table to ensure that incoming data is properly stored and queries remain optimized.

To avoid manual intervention, we use:

  • A stored procedure (PIE_OWNR.ADD_NEXT_MONTH_PARTITION) to generate and execute a dynamic SQL statement that creates the required partition.
  • An Oracle DBMS_SCHEDULER job (ADD_MSG_MON_PARTITION_JOB) to automatically execute the procedure on the 28th of every month.
  • This ensures that the partition for the upcoming month is always created in advance, reducing the risk of runtime errors and easing operational overhead.

📜 Step 1: The Stored Procedure – PIE_OWNR.ADD_NEXT_MONTH_PARTITION

This procedure calculates the first day of the next month and generates a partition name like P_AUG25. It then constructs a dynamic ALTER TABLE statement to add a new partition into the PIE_MSG_MONITOR table, covering all timestamps from the first day of that month up to (but not including) the first day of the following month.

Here’s the full implementation:

CREATE OR REPLACE PROCEDURE PIE_OWNR.ADD_NEXT_MONTH_PARTITION AS
  v_next_partition_date DATE := TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM'); -- 1st of next month
  v_partition_name      VARCHAR2(30);
BEGIN
  v_partition_name := 'P_' || TO_CHAR(v_next_partition_date, 'MONYY');

  EXECUTE IMMEDIATE '
    ALTER TABLE PIE_OWNR.PIE_MSG_MONITOR ADD PARTITION ' || v_partition_name || '
    VALUES LESS THAN (TIMESTAMP''' || TO_CHAR(ADD_MONTHS(v_next_partition_date, 1), 'YYYY-MM-DD') || ' 00:00:00'')
    TABLESPACE NGCSDAT02
    PCTFREE 10 INITRANS 1 MAXTRANS 255
    STORAGE (
      INITIAL 64K
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
    )';
EXCEPTION
  WHEN OTHERS THEN
    -- If the partition already exists, ignore the error
    IF SQLCODE = -14701 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

🧠 How It Works:

  • v_next_partition_date determines the first day of the next month.
  • The partition name is formatted using TO_CHAR (e.g., P_AUG25).
  • The ALTER TABLE command is dynamically executed to add a new partition with an upper bound of the 1st of the month after next, which ensures the partition spans the entire next month.
  • If the partition already exists, Oracle error -14701 is caught and ignored.

🕒 Step 2: The DBMS Scheduler Job – ADD_MSG_MON_PARTITION_JOB

To automate the partition creation every month, we define a scheduler job that will run the procedure on the 28th of each month. This gives enough buffer time before the new month starts, ensuring the partition is available before any data starts flowing in.

Here’s the job definition:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'ADD_MSG_MON_PARTITION_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PIE_OWNR.ADD_NEXT_MONTH_PARTITION',
    start_date      => TRUNC(SYSDATE, 'MM') + 27, -- Runs on the 28th of the current month
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=28',
    enabled         => TRUE,
    comments        => 'Adds next month''s partition to PIE_MSG_MONITOR'
  );
END;
/
Enter fullscreen mode Exit fullscreen mode

🔄 How It Works:

  • job_type is set to 'STORED_PROCEDURE', indicating the job will execute a PL/SQL procedure.
  • job_action specifies the procedure to be called.
  • start_date is calculated to start on the 28th of the current month using TRUNC(SYSDATE, 'MM') + 27.
  • repeat_interval ensures the job runs monthly on the 28th.
  • enabled => TRUE activates the job immediately.

✅ Conclusion

By combining a dynamic partition-adding procedure with Oracle’s DBMS_SCHEDULER, we’ve created a clean, reusable, and fully automated mechanism to manage monthly partitions in the PIE_MSG_MONITOR table. This approach minimizes the risk of runtime errors due to missing partitions, improves maintainability, and ensures that the system is always one step ahead of incoming data.

This is a practical and elegant solution for any Oracle-based system that relies on time-partitioned tables and aims for zero downtime and minimal manual intervention in partition management.

Top comments (0)