How to create or alter a job

So, basically, you can use the following code to create a job.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'JOB_NAME',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN NULL; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=10,11,12,13,14; BYMINUTE=0;',
        enabled => TRUE,
        comments => 'First job schedule');
END;

If you want to modify the REPEAT_INTERVAL attribute of a job, you can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure for this purpose.

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
        name => 'JOB_NAME',
        attribute => 'repeat_interval',
        value => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=14; BYMINUTE=0; BYSECOND=0;');
END;

Run a job manually. By default job runs in the current session. If you want it to run in another session, you should set the use_current_session parameter to false.

BEGIN
    DBMS_SCHEDULER.RUN_JOB(
        job_name => 'JOB_NAME',
        use_current_session => FALSE --default value is TRUE
        );
END;

Enable a job.

BEGIN
    DBMS_SCHEDULER.ENABLE('JOB_NAME');
END;

Disable a job.

BEGIN
    DBMS_SCHEDULER.DISABLE('JOB_NAME');
END;

Stop a running job. If force is set to TRUE, the Scheduler immediately terminates it. Moreover, you need to be granted the MANAGE SCHEDULER system privilege in order to use force option.

BEGIN
    DBMS_SCHEDULER.STOP_JOB(
        job_name => 'JOB_NAME',
        force => true --optional
        );
END;

By default, if you attempt to drop a running job without specifying the force parameter, Oracle will raise an error indicating that the job is running and cannot be dropped. However, if you include the force parameter and set it to true, Oracle will stop the running job and then drop it from the database. This parameter allows you to remove the job regardless of its current state. The same applies for stopping a job.

Drop a job.

BEGIN
    DBMS_SCHEDULER.DROP_JOB (
        job_name => 'JOB_NAME',
        force => true --optional
        );
END;

In addition, you can execute the following query to list your scheduler jobs.

SELECT * FROM USER_SCHEDULER_JOBS;
Posted in DBA

One thought on “How to create or alter a job

Leave a Reply

Your email address will not be published. Required fields are marked *