Home > DBA's day, Definitions, linux, oracle > Oracle Jobs

Oracle Jobs


If you are want to schedule a job in the Oracle Database to execute a certain task regularly, basically you can use one of these methods:

1. Oracle Scheduler (DBMS_SCHEDULER):

DBMS_SCHEDULER is a powerful feature that you can use to schedule jobs on the database, which can be very useful for certain DBA tasks like indexes rebuilding, backups checking or data consistency.

To view the information about currently running jobs, you can execute this query:

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

With this query you can check all the scheduled jobs into the database:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

It is a very strong feature and very recommended currently. If you want to find more information about the scheduler, check the Oracle documentation in this link:

https://docs.oracle.com/database/121/ARPLS/d_sched.htm

2. Oracle Jobs (DBMS_JOB):

DBMS_JOB is a deprecated feature but you can still use it even in Oracle 12c, even if it started in Oracle 7!

The DBMS JOB feature is a lot less intuitive to use, although some DBAs state that it is more flexible. In any case, either if you want to take advantage of its flexibility or if you need to understand how it works because your database already uses this feature, it is another option.
You can find the Oracle documentation here:

https://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66577

And a very good and practical post explaining how to use DBMS_JOB:

http://www.orafaq.com/node/871
3. Crontab (Unix/Linux) / Windows Task Scheduler

The third option is to externalize the task execution to the Operating System, so that the tasks are executed through a .sh in Unix, or a .bat in Windows, executing later a .sql to the database.

This method is easier to manage if you are already using crontab or Task Scheduler for other tasks not related with the database, but the disadvantage is that it is node dependent so it can be a problem in the case of Oracle RAC.
Do you know any other method to schedule jobs in Oracle? What is your prefered one?

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: