Oracle PL/SQL for DBAs

Simple Job Management

Now that you understand basically how to create a job, let’s see how to manage the job we just created. Unless otherwise stated, all tasks are performed by various programs in the DBMS_SCHEDULER package.

Enabling and disabling jobs

You can temporarily disable or enable a job using the DISABLE and ENABLE procedures, respectively. For example, to disable the job RMAN_INC, specify:

    BEGIN
        DBMS_SCHEDULER.disable (NAME => 'RMAN_INC');
    END;

If this job is currently running, the above command will return an error. You can include the FORCE parameter to override the default behavior:

    BEGIN
        DBMS_SCHEDULER.disable (NAME => 'RMAN_INC', FORCE=> TRUE);
    END;

Specifying FORCE will disable the job, but will allow the currently running process to complete.

You can enable a currently disabled job by calling the procedure ENABLE as follows.

    BEGIN
       DBMS_SCHEDULER.enable (NAME => 'RMAN_INC');
    END;

Tip

When you create a job and do not set the enabled parameter, the job is created but is disabled. You must explicitly enable it.

Stopping running jobs

When a job is running and you want to stop it, you can run the STOP_JOB procedure as follows.

    BEGIN
       DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC');
    END;

STOP_JOB will attempt to gracefully stop a job. There are times, unfortunately, when a graceful shutdown is not possible, in which case the above statement returns an error. You can force a job to shut down by using the FORCE parameter. For example, to stop the job APPLY_INTEREST (which executes ...