How to Use DBMS_SCHEDULER to manage scheduled JOB

In ORACLE databases, the tools used to execute scheduled tasks are generally DBMS_JOB packages. This package is easy to use and has a long history and is still widely used. Frankly speaking, this package

In ORACLE databases, the tools used to execute scheduled tasks are generally DBMS_JOB packages. This package is easy to use and has a long history and is still widely used. Frankly speaking, this package

In Oracle databases, the tools used to execute scheduled tasks are generally DBMS_JOB packages. This package is easy to use and has a long history and is still widely used. Frankly speaking, this package's functionality satisfies the scheduling task running needs of most applications, so the vitality is so strong.

However, let's talk about its shortcomings here.

In DBMS_JOB usage, we need to know the execution time of each JOB to determine the execution time of the stored procedure called by the JOB. Here you will say that this can also be implemented in the stored procedure. In the stored procedure, add a timestamp before and after the execution to the log table.

Yes. We used to do this when dealing with such requirements.

But since ORACLE 10.1, ORACLE has developed a new package DBMS_SCHEDULER. This package is so complicated that I didn't use it again after my first study. The feature is so powerful that we don't need much of it.

Let's see how DBMS_SCHEDULER solves this problem.

In SQLPLUS, use DBMS_SCHEDULER.CREATE_JOB to create a scheduled task for scheduling a stored procedure.

The storage process is simple. I created this test to insert data into a table.

Create table T1 as select sysdate as aa from dual;

Create or replace procedure SP_TEST_T1

BEGIN

Insert into T1 selectd sysdate from dual;

COMMIT;

END;

This stored procedure does not have input/output parameters, so it is convenient for JOB calling. If there are parameters in the actual running process, we will write a stored procedure to encapsulate it and then put it into the JOB for calling.

For example:

Create procedure GATHER_GTJA_STATS

AS

BEGIN

SYS. DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => 'gtja ', ESTIMATE_PERCENT => 30, METHOD_OPT =>' for all indexed columns size auto', CASCADE => TRUE, OPTIONS => 'gather ');

END;

Use DBMS_SCHEDULER.CREATE_JOB to create a JOB. Call the Stored Procedure SP_TEST_T1. The execution interval is 2 minutes. The JOB_NAME name can be defined by yourself. This removes the disadvantage that the JOB number in DBMS_JOB cannot be customized.

EXEC DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'job _ SP_TEST_T1 ', JOB_TYPE => 'stored _ PROCEDURE', JOB_ACTION => 'SP _ TEST_T1 ', START_DATE => SYSDATE, REPEAT_INTERVAL => 'freq = MINUTELY; INTERVAL = 2 ');

After the command is successfully executed, you can view the JOB configuration value in * _ SCHEDULER_JOBS.

SELECT * FROM DBA_SCHEDULER_JOBS;

SELECT * FROM USER_SCHEDULER_JOBS WHERE JOB_NAME = 'job _ SP_TEST_T1 ';

By default, jobs created using this command are not executed. You need to change the JOB status to the executable status.

EXEC DBMS_SCHEDULER.ENABLE (NAME => 'job _ SP_TEST_T1 ');

You can delete and recreate it.

EXEC DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'job _ SP_TEST_T1 ');

EXEC DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'job _ SP_TEST_T1 ', JOB_TYPE => 'stored _ PROCEDURE', JOB_ACTION => 'SP _ TEST_T1 ', START_DATE => TO_DATE ('2017-06-12 15:30:00 ', 'yyyy-MM-DD HH24: MI: ss'), REPEAT_INTERVAL => 'freq = DAILY ');

This creation process is actually not much different from creating a JOB in DBMS_JOB, but there are too many configurable tasks in DBMS_SCHEDULER. The execution interval is very flexible. I will not detail it here. For details, please refer to the official ORACLE documentation.

DBMS_SCHEDULER records the execution time of each JOB. Therefore, you can query the execution time of each JOB.

For more information, see * _ SCHEDULER_JOB_LOG and * _ SCHEDULER_JOB_RUN_DETAILS.

SELECT * FROM DBA_SCHEDULER_JOB_LOG;

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

If there are too many logs, you can use PURGE_LOG to clear them.

EXECUTE DBMS_SCHEDULER.PURGE_LOG;

This DBMS_SCHEDULER package has too many functions and is also exhausting to use, so many people still choose DBMS_JOB.

See the Introduction of the differences between the two packages and extract them for reference.

At first glance it looks like only other names with more human readable schedules fordbms_scheduler, compared to dbms_job. when looking slightly better, there are loads of differences, even in Oracle 10gR1. currently we are in 11gR2. every release dbms_scheduler gets more enhancements, where dbms_job has been static for policyears.

Differences

Dbms_sched1_has logging

Dbms_sched1_has external jobs

Dbms_sched1_has job chains

Dbms_scheduler has job event handling (can raise and react upon events)

Dbms_scheduler has resource manager integration

Dbms_sched1_has human readable calendar syntax

Dbms_scheduler can combine different calendars in a new one

In 11g extra

Dbms_sched1_has remote external jobs

Dbms_scheduler has light weight jobs-generate tables low overhead jobs in one tx

Dbms_scheduler can send mail on job completion

Dbms_sched1_jobs can have multiple targets

Dbms_job can only run pl/SQL type of jobs in the current database.

I hope this (in complete list) h