DBA Tips Archive for Oracle

  


Introduction to DBMS_JOB

by Alex Gaethofs <Alex.Gaethofs@elia.be>, Database Administrator

Overview

It is possible to setup a kind of automatic batch that would be lauched every night in order to clean up tables using truncate-statements to avoid useless redo log creation.

This month, Alex Gaethofs introduces how to perform nightly clean up jobs while introducing the DBMS_JOB package.

DBMS_JOB

DBMS_JOB is an Oracle PL/SQL package provided to users. It is available with PL/SQL 2.2 and higher. DBMS_JOB allows a user to schedule a job to run at a specified time. A job is submitted to a job queue and runs at the specified time. The user can also input a parameter that specifies how often the job should run. A job can consist of any PL/SQL code.

To run jobs using DBMS_JOB you have to specify two parameters in the init.ora of the database :

    # Simultaneous job listeners for running batch jobs (every 2 minutes=120)
    JOB_QUEUE_PROCESSES=10
    JOB_QUEUE_INTERVAL=120
Remember restarting the instance after modifying the init.ora.

SNP processes run in the background and implement database snapshots and job queues. Without an SNP process (JOB_QUEUE_PROCESSES = 0) the DBMS_JOB package cannot run automatically.

Setup

    Step 1 : Connect to the database as application-owner.
    In the example below, "ALEX" is the owner of the schema.

    Step 2 : Create a package called "Batch_Job"


      Package Specification 
      --------------------------------

      PACKAGE Batch_Job IS

      PROCEDURE submit(
        p_job       OUT INTEGER,
        p_what      IN VARCHAR2,
        p_next_date IN DATE     DEFAULT SYSDATE,
        p_interval  IN VARCHAR2 DEFAULT 'null',
        p_no_parse  IN BOOLEAN  DEFAULT FALSE
      );

      PROCEDURE remove(p_job IN INTEGER);

      PROCEDURE disable_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2);
      PROCEDURE truncate_table(p_table_name IN VARCHAR2);
      PROCEDURE enable_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2);
      PROCEDURE run_daily_morning_job;
  
      END Batch_Job;

      Package Body
      ---------------------

      PACKAGE BODY Batch_Job IS

      l_job NUMBER := 0;

      PROCEDURE submit(
        p_job   OUT INTEGER,
        p_what      IN VARCHAR2,
        p_next_date IN DATE     DEFAULT SYSDATE,
        p_interval  IN VARCHAR2 DEFAULT 'null',
        p_no_parse  IN BOOLEAN  DEFAULT FALSE
      ) IS
        BEGIN
          DBMS_JOB.submit(p_job, p_what, p_next_date,p_interval,p_no_parse);
        END submit;
  
        PROCEDURE remove(p_job IN INTEGER) IS
        BEGIN
          DBMS_JOB.remove(p_job);
        END remove;

        PROCEDURE disable_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2) IS
        BEGIN
          EXECUTE IMMEDIATE('ALTER TABLE '||p_table_name||' DISABLE CONSTRAINT '||p_constraint_name);
        END disable_constraint;
  
        PROCEDURE truncate_table(p_table_name IN VARCHAR2) IS
        BEGIN
          EXECUTE IMMEDIATE('TRUNCATE TABLE '||p_table_name);
        END truncate_table;
  
        PROCEDURE enable_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2) IS
        BEGIN
          EXECUTE IMMEDIATE('ALTER TABLE '||p_table_name||' ENABLE CONSTRAINT '||p_constraint_name);
        END enable_constraint;

        /* Start defining the batch jobs to run */
        PROCEDURE run_daily_morning_job IS
        BEGIN
          Batch_Job.Submit(l_job,'daily_morning_job;',sysdate,'TRUNC(sysdate)+1+1/288');
        END run_daily_morning_job; 

        PROCEDURE run_daily_night_job IS
        BEGIN
          Batch_Job.Submit(l_job,'daily_night_job;',sysdate,'TRUNC(sysdate)+1+1/288');
          NULL;
        END run_daily_night_job; 

        END Batch_Job;

    Step 3 : Create a procedure called "daily_morning_job" :

      PROCEDURE daily_morning_job IS
        BEGIN
          Batch_Job.disable_constraint('ALEX_EMP','SYS_C001205');
          Batch_Job.truncate_table('ALEX_DEPT');
          Batch_Job.truncate_table('ALEX_EMP');
          Batch_Job.enable_constraint('ALEX_EMP','SYS_C001205');
        END;

        If someone wants to truncate other tables, he just needs to modify
        the procedure "daily_morning_job", add the necessary 
        truncate-instructions. The day after, at exactly 00:05, 
        the updated version of the procedure "daily_morning_job" will run.

        As you can see in the package "batch_job", another job called
        "daily_night_job" is almost available. You just have to remove 
        the remarks and create a procedure "daily_nigh_job".

    Step 4 : Check the view 'USER_JOBS' to findout jobs you have submitted 
             in the job-queue.

      SELECT job,what,next_date,next_sec FROM user_jobs;
Summary
    How do you submit a DBMS_JOB ?

    SQL>DECLARE l_job NUMBER := 0;
    SQL>BEGIN
    SQL> DBMS_JOB.SUBMIT(l_job,'procedure_name;',sysdate,TRUNC(sysdate)+1+1/288);
    SQL>END;
    SQL>/

    How do we resubmit our job ?

    SQL>EXECUTE batch_job.run_daily_morning_job;

    REMEMBER : The first time the job is being run it will run immediately.
               The next time the job will run is specified with the interval 
               parameter of the DBMS_JOB package.

    How do you remove a submitted DMBS_JOB  ?
	
    SQL>EXECUTE DBMS_JOB.REMOVE(jobno);


    Some additional documentation which can help you by setting up a job
    mechanisme :

    Note 74149.1 : Invoker Rights versus Definer Rights in Oracle 8i
    Using PL/SQL Version 2 packages in Developer 2000 (Author : Chris Halioris)



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 03-May-2000 00:00:00 EDT
Page Count: 71036