How to Automate Chores with the Oracle 12c Scheduler - dummies

How to Automate Chores with the Oracle 12c Scheduler

By Chris Ruel, Michael Wessler

With the use of the Oracle 12c Scheduler, you can run almost any type of program with a robust resource-management and scheduling system. The Scheduler is intended to help you create and automatically run many of your administrative tasks managed from within the database. Oracle Scheduler is implemented via the DBMS_SCHEDULER database package and is a replacement for the old DMBS_JOB package.

The Scheduler can run these programs:

  • PL/SQL stored procedures

  • PL/SQL anonymous blocks

  • Java stored programs

  • Local and remote external programs such as shell scripts and executables

You can schedule jobs that are

  • Timed-based: A job can run simply from wall-clock time. It can repeat on a schedule based on hours, days, months, and so on.

  • Event-based: The results of certain conditions or events in the environment can cause a job to run. This trigger is useful when you have to wait for other processes to finish before a job is run.

  • Dependency-based: You can set up dependency such as success or failure. Depending on the outcome of one job, one or more dependent scenarios can be executed.

Oracle 12c scheduler objects

The Scheduler can use a number of objects to run jobs. Not all of them are mandatory. These objects specify job parameters, timing, execution windows, and resource limits.

  • Programs: Programs are the actual code that the Scheduler will run. They identify the execution code, arguments, and job type.

  • Schedules: The job schedules are just what you think. They contain parameters such as when and how often. A schedule should be created by the DBA and then shared for many jobs.

  • Jobs: When a job object is created, it contains the executable and the schedule required to run the job. You can enable the job for it to begin the task based on the parameters. Jobs are categorized as any of the following:

    • Database jobs run out of the database from PL/SQL commands.

    • External jobs run off the operating system from external executables.

    • Chain (Dependency) jobs run based on status of other jobs.

    • Detached jobs run to simply kick off another job in a new process.

    • Lightweight are simple jobs that exist only for their immediate execution. They aren’t stored as schema objects. They’re used for quick, low-overhead applications.

  • Windows: Helps schedule jobs for certain times, which can help control resource usage. When a window becomes active, certain resource directives are enabled that might restrict a job from overwhelming the system.

How to disable a job

You can disable a job after it’s completed. That way, if necessary, you can easily re-enable it later. To disable your job, type the following:

<exec dbms_scheduler.disable(‘my_user_count_job’)>

You should see this:

PL/SQL procedure successfully completed.

How to remove the job

If your job is no longer needed, you can remove just the job and leave the program out there, or you can remove both. Same goes for the schedule you created.

If you no longer need this particular job, you can remove it by typing

<exec dbms_scheduler.drop_job('my_user_count_job')>

You should see this:

PL/SQL procedure successfully completed.

If you no longer need your program, you can remove it by typing

<exec dbms_scheduler.drop_program('prog_insert_user_count')>

You should see this:

PL/SQL procedure successfully completed.

If you no longer need a particular schedule, remove it by typing

<exec dbms_scheduler.drop_schedule('my_weekend_5min_schedule')>

You should see this:

PL/SQL procedure successfully completed.

The job schedule you created can be used for multiple jobs; be careful when removing your schedule to ensure you aren’t impacting more than what you expect.