Mastering Oracle Scheduler in Oracle 11g Databases

5 (1 reviews total)
By Ronald Rood
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Simple Jobs

About this book

Scheduler (DBMS_SCHEDULER) is included in Oracle Database and is a tool for the automation, management, and control of jobs. It enables users to schedule jobs running inside the database such as PL/SQL procedures or PL/SQL blocks, as well as jobs running outside the database like shell scripts. Scheduler ensures that jobs are run on time, automates business processes, and optimizes the use of available resources.

You just need to specify a fixed date and time and Scheduler will do the rest. What if you don't know the precise time to execute your job? Nothing to worry about, you can specify an event upon which you want your job to be done and Scheduler will execute your job at the appropriate time. Although scheduling sounds quite easy, it requires programming skills and knowledge to set up such a powerful, intelligent scheduler for your project.

This book is your practical guide to DBMS_SCHEDULER for setting up platform-independent schedules that automate the execution of time-based or event-based job processes. It will show you how to automate business processes, and help you manage and monitor those jobs efficiently and effectively. It explains how Scheduler can be used to achieve the tasks you need to make happen in the real world. With a little understanding of how the Scheduler can be used and what kind of control it gives, you will be able to recognize the real power that many known enterprise-class schedulers – with serious price tags – cannot compete with.

You will see how running a specific program can be made dependent on the successful running of certain other programs, and how to separate various tasks using the built-in security mechanisms. You will learn to manage resources to balance the load on your system, and gain increased database performance.

Publication date:
June 2009
Publisher
Packt
Pages
240
ISBN
9781847195982

 

Chapter 1. Simple Jobs

For this book, I carried out an out of the box installation on Enterprise Linux with the Oracle-validated package applied to it. I made a simple database using DBCA, complete with sample schemas and DB Console. For storage, I made use of an NAS and openfiler on a separate installation presented as iSCSI targets.

Let's get started with simple jobs.

Simple jobs are the jobs that can easily be created using DB Console with little or no extra object creations required. The quickest way to get you started is by making the database perform actions for you. A simple job is not the same as a useless job. Far from that, many tasks can be performed using simple jobs. Sometimes we can go back and use easy, single-step jobs rather than using advanced jobs based on complicated rules.

The DB Console does provide some support for the Scheduler, which can be used for simple tasks. However, for more elaborate things we need to use a PL/SQL editor. Any PL/SQL-capable tool will do. If you like, use SQL*Plus, SQL Developer, DbVisualizer, or TOAD for a Windows-centric client. Let's start using DB Console and see what this gives us.

This chapter covers:

  • Creation of a user who can handle the tasks

  • A job that runs PL/SQL code

  • A job that can start a stored procedure

  • A job that runs an executable

Creating a user

When working with simple jobs, our basic need is to create a user. In DB Console, there is a default user sysman. We can use this or the user system. However, it is better to create a dedicated user for different tasks. This prevents us from giving too many privileges to jobs and maintains auditability. So, let's first create a normal user who does the work for us:

create user marvin identified by panic;
grant create session, create job to marvin;
grant select any dictionary to marvin;
create user stats identified by nopanic;
alter user stats quota unlimited on users;
create table stats.session_log as select * from v$session where 1 = 2;
create table stats.session_stat_log as select * from v$mystat where 1 = 2;
grant select,insert,update,delete on stats.session_log to marvin;
grant select,insert,update,delete on stats.session_stat_log to marvin;
create public synonym session_log for stats.session_log;
create public synonym session_stat_log for stats.session_stat_log;

The select any dictionary privilege is mainly because we want to use DB Console. For this, it must view a lot from the dictionary. Now start a web browser and connect to the freshly created user marvin. The Oracle Scheduler support is provided in the Server tab of the DB Console.

 

Creating a user


When working with simple jobs, our basic need is to create a user. In DB Console, there is a default user sysman. We can use this or the user system. However, it is better to create a dedicated user for different tasks. This prevents us from giving too many privileges to jobs and maintains auditability. So, let's first create a normal user who does the work for us:

create user marvin identified by panic;
grant create session, create job to marvin;
grant select any dictionary to marvin;
create user stats identified by nopanic;
alter user stats quota unlimited on users;
create table stats.session_log as select * from v$session where 1 = 2;
create table stats.session_stat_log as select * from v$mystat where 1 = 2;
grant select,insert,update,delete on stats.session_log to marvin;
grant select,insert,update,delete on stats.session_stat_log to marvin;
create public synonym session_log for stats.session_log;
create public synonym session_stat_log for stats.session_stat_log;

The select any dictionary privilege is mainly because we want to use DB Console. For this, it must view a lot from the dictionary. Now start a web browser and connect to the freshly created user marvin. The Oracle Scheduler support is provided in the Server tab of the DB Console.

 

Running your Jobs


There are several kinds of jobs that we can run. The kind of job is defined by the job_type. What a job does is defined by the job_action. In this chapter, we will see an example of a job that calls:

  • A PL/SQL block

  • A stored procedure

  • An external script

  • A program

Now let's see each one in detail.

PL/SQL block

The simplest type of job is the one that runs a PL/SQL block as shown here. A PL/SQL block as a job action is just that—a normal anonymous block of PL/SQL code. The advantage of this type is that it is very simple to set up as it does not need anything else in the database to be defined. As we cannot give any arguments to a PL/SQL block, we cannot use arguments for this type of job—something to remember when you try to create a library of reusable code.

Once we are successfully connected as MARVIN, the DB Console shows us the database Home page. From here, we select the Server tab to reach the page presented in the following screenshot. We are interested in the Oracle Scheduler column. All the implementations towards using the jobs are done here. We can do a lot of things in DB Console. However, for now we will restrict ourselves to making a Job. Click on the Jobs entry in the Oracle Scheduler column as shown here:

Click on the Jobs entry in the Oracle Scheduler column. In the screen that follows, we can see which jobs are already defined—or better, jobs that are yours or the jobs on which you have privileges:

Apparently, marvin doesn't own jobs and has no privileges at all to run any job. We are going to change this quickly. To do so, click on the Create button to bring us to the Create Job screen where most of the job definition is handled.

It shows us a few of the job properties that can be entered, selected, or switched as follows:

Here we can enter the properties of the job such as Name, Command Type of the job, and the action of the job. We can see what is entered. Because the default job type is of type PL/SQL block, we did not have to change anything for job type to get this going. Other attributes to note are the Enabled state and Logging Level.

If we create a job that has no other thing defined on it that regulates the execution of the job, such as a schedule or a window, the job will start running as soon as it is successfully created.

In the PL/SQL block, we can enter whatever code we want, as long as it is valid PL/SQL. In this case, there are a few simple inserts in a table.

Click on the Show SQL button. It will show the following code:

BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"MARVIN"."TEST01"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into session_log select * from v$session where sid = (select sid from v$mystat where rownum = 1);
insert into session_stat_log select * from v$mystat;
end;',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'a simple test',
auto_drop => FALSE,
enabled => TRUE
);
sys.dbms_scheduler.set_attribute
(
name => '"MARVIN"."TEST01"',
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_FULL
);
sys.dbms_scheduler.enable( '"MARVIN"."TEST01"' );
END;

Now, if we go back to the Create Job screen and click on the OK button, the job will be created and we will again come to the Scheduler Jobs screen. This time the list is no longer empty. It not only shows us our precious little job, but it also shows us that the job is in the RUNNING state as shown in the following screenshot:

In case you do not want a job to start running directly upon creation, create it in the disabled state. In this case, we created the job in the Enabled state and so the job starts running automatically. To run it once more, just click on the Run Now button and off it goes. All this is done without having to use a command-line tool. In the rest of the book, all examples will be using good old SQL*Plus, SQL Developer, or DbVisualizer. The choice among them is mostly influenced by the contents of my glass!

Stored procedure

The next simplest type of job is the one that executes a stored procedure. A stored procedure is a piece of PL/SQL code that is stored in the database. It's not that this is more difficult; it's merely a question of creating the procedure and selecting it as job_action. In order to be able to select the procedure, we have to get the privileges to create the procedure. We can also select a procedure from another schema to use that in a job. In that case, we need the execute privilege on that procedure. However, we will create our own procedure. To be able to do that, grant marvin the correct privilege:

Grant create procedure to marvin;

And create a procedure called SNAP. It does not do much; it has the same contents as the PL/SQL block in the previous TEST01 job:

CREATE OR REPLACE PROCEDURE SNAP as
begin
insert into session_log select * from v$session where sid = (select sid from v$mystat where rownum = 1);
insert into session_stat_log select * from v$mystat;
end SNAP;

In DB Console, we need to change the Command Type by clicking on the Change Command Type button as shown in the following screenshot:

This brings us to the next screen where we can select the appropriate job type. In this case, it is Stored Procedure as shown in the following screenshot:

We can use the torch icon to select the procedure we want to run by the job as shown in the following screenshot:

Here, we select the SNAP procedure from MARVIN.

After clicking on the Select button from near the bottom of the screen, we get back to where we started from—that is, our selection—but this time with the procedure name entered as shown in the following screenshot:

Clicking on the OK button brings us back to the main job creation screen as shown in the following screenshot:

Using the Show SQL button will reveal the code that is generated for us:

BEGIN
sys.dbms_scheduler9.create_job
(
job_name => '"MARVIN"."TEST02"',
job_type => 'STORED_PROCEDURE',
job_action => '"MARVIN"."SNAP"',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => TRUE
);
END;

The job performs the same function as the previous example, but this time by calling a stored procedure that has to do the work. Sometimes, a job type PL/SQL is sufficient for the task. But if you are planning to use job arguments, you can't use a job type of PL/SQL. In that case, you will need a job type of stored procedure or program, which we will discuss later.

Executable

A job type that starts an executable is the last type that can be created in the DB Console without having to create another Scheduler object type first. As compared to the DB-only PL/SQL or stored procedure, an executable can be an operating system script or binary program which the user nobody has execution privileges on. As the user nobody is not exactly a very powerful user, this mostly means that the script or binary program has to be executable by everybody. As this kind of job performs code that is not in the database, we also call it an external job. Later in the book, we will discover the preferred alternative for external jobs, remote external jobs, which is introduced in Oracle 11g.

To be able to create external jobs, marvin needs the extra privilege.

Grant create external job to marvin;

The code could very well look like this:

BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"MARVIN"."TEST03"',
job_type => 'EXECUTABLE',
job_action => '/tmp/testje.sh',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'an external job test',
auto_drop => FALSE,
enabled => TRUE
);
END;

And the script testje.sh is here:

#!/bin/ksh
{
id
env
} >/tmp/testje.log 2>&1

This may not be the most complex script, but it does show some important things about how the job is run. Without this information, it's a bit hard to get a working script. As most of us know, jobs that are launched by cron have a simple environment. But jobs started by the Oracle Scheduler win the game when it comes to simplicity. The environment is almost empty:

uid=99(nobody) gid=99(nobody) groups=99(nobody) context=user_u:system_r:unconfined_t
_=/bin/env
PWD=/

The user who ran the script is by default the user nobody. The script that is started has slash (/) as working directory. So don't look surprised when you get hit by errors such as "permission denied". Another surprise might be that there is no job output shown in the Scheduler views—that is, if the job succeeds. Strangely enough, Oracle decided to show only job outputs in the Scheduler views when the job fails. The job gets a FAILED status when the error returned is other than 0. The error code is interpreted by Oracle using the regular errno.h, which we all know from the good old C language hacking days. So if you invent an exit code, Oracle will interpret it using the standard error codes. The good news is that the first few bytes of stderr are logged in the ADDITIONAL_INFO column of the *_scheduler_job_run_details view. The stderr is always logged, even when the error code is 0.

Program

A program is a database object that can be used to build a library of building blocks to create jobs or job chains. Just like a job, a program can be chosen from a few types such as:

  • A PL/SQL block

  • A stored procedure

  • An executable

A program is not a runnable job by itself. It contains what a job or chain step should execute. It references the code and can easily be reused. When including programs in job steps, there are some limitations. But as long as we can do without arguments, we are OK. Let's create a few programs for the actions created above. When picking names, we need to think about the namespace where the jobs and programs live. They live in the same namespace. This means that if the TEST01 job exists, I cannot create a program called TEST01 as shown in the following screenshot. So, I will prefix the programs with P_.

The TEST01 program is prefixed by P_, which gives P_TEST01. Now, don't forget to enable the program. Unlike jobs, the programs are not started at the enable time. In this case, it is just made useable for jobs or chains. Also, create the P_TEST02 program that selects the stored procedure SNAP, and P_TEST03 that calls the executable.

 

Defining arguments for your jobs


When using building blocks like stored procedures and programs, we might want to control what the code is going to do for us by giving it arguments. Arguments can also be given to external jobs such as command-line arguments. There are two types of arguments:

  • Metadata arguments

  • Normal application arguments

If you are using arguments for a job, you will start by specifying the number of arguments that the procedure, program, or external job is going to have. Next, you need to define the arguments and their values. Until all of the arguments are defined, it is not possible to enable the item for which you are defining the arguments.

Metadata arguments

A metadata argument is an argument that tells something about the currently running job.

There are several reasons for using metadata arguments in a program. One is that a program is executed by several different jobs and you want to know which job called the program. The job_name is one of the important metadata attributes for a metadata argument. The complete list of useable attributes is as shown in the following table:

Attribute name

Description

job_name

Name of the currently running job.

job_subname

Subname of the currently running job, if running in a chain. The combination of job_name and job_subname define the step that the chain is running.

job_owner

The owner of the currently running job.

job_scheduled_start

This tells when the job was scheduled to start.

job_start

This tells when the job really started.

window_start

If the job is connected to a window, this specifies the window open time.

window_end

If the job is started by a window, this specifies the time at which the window is scheduled to close.

event_message

The contents of the event message that triggered an event-driven job.

This does look a bit mysterious, but a simple example will make things clearer. In this example, we make a small program that uses the job_name attribute to find out which job called the program.

First, give marvin the required privileges and quota to be able to create a log table:

Grant create table to marvin;

Alter user marvin quota unlimited on users;

Next, let marvin create the log table:

Create table log (job_name varchar2(30), dat_sys date);

Define a stored procedure that accepts one argument, the job_name:

--/
CREATE OR REPLACE PROCEDURE WHOCALLEDME (v_job varchar2) as
begin
insert into log (job_name, dat_sys) values (v_job, sysdate);
end WHOCALLEDME;
/

Now, create a program that uses this stored procedure:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => '"MARVIN"."P_CALLER"',
program_action => '"MARVIN"."WHOCALLEDME"',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'show which job called me',
enabled => FALSE
);
end;
/

This program is created with enabled as FALSE. As not all of the arguments are defined, it cannot be enabled. Trying to run this code with enabled=> TRUE will result in an error message that will try to explain to us that not all arguments are defined. Now it is time to define the metadata argument to complete the job.

--/
begin
DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT
(
program_name => 'P_CALLER',
metadata_attribute => 'job_name',
argument_position => 1,
argument_name => 'v_job'
);
end;
/

The program is now completely defined with all of the arguments, so we can enable the program. If this program is called by some job, the Scheduler automatically inserts the correct values in the first argument of this program.

--/
BEGIN
dbms_scheduler.enable('P_CALLER');
END;
/

Now create the job that uses the enabled program, P_CALLER. The job name is test_m_arg and is defined as follows:

--/
BEGIN
dbms_scheduler.create_job
(
job_name => 'TEST_M_ARG',
program_name => 'P_CALLER',
comments => 'test using metadata argument',
enabled => TRUE
);
END;
/

Because the job is enabled at the creation time and we have not tied the job to a schedule, an event, or a window, the job immediately starts running. So it makes sense to check the contents of the log table. Despite the fact that the job itself has no arguments, the job name is passed to the program that inserted it into the log table.

Select job_name from log;

This clearly reveals the name of the job that caused the program to be executed: TEST_M_ARG.

This example might look a bit silly, but this mechanism gives us countless possibilities. The other attributes have their own different uses. For example, the window_end attribute enables a job to find out how much time it has to complete its task before the window that started the job will close. This can help the code to decide whether or not to make an extra iteration to complete another batch of transactions.

Normal application arguments

Now that we have seen the mysterious metadata arguments, the normal application arguments, also known as regular arguments, are just a piece of cake. For this example, we use the same log table as for the metadata example. We create a separate procedure that also has one argument as follows:

--/
CREATE OR REPLACE PROCEDURE justaproc (v_arg varchar2) as
begin
insert into log (job_name, dat_sys) values (v_arg, sysdate);
end justaproc;
/

The procedure used by a program is defined as follows:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'P_ARG01',
program_action => 'JUSTAPROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'pass an argument',
enabled => FALSE
);
END;
/

Now let's define the argument to complete the program description:

--/
BEGIN
DBMS_SCHEDULER.DEFINE_program_ARGUMENT
(
program_name => 'P_ARG01',
argument_position => 1,
argument_name => 'v_arg',
argument_type => 'varchar2'
);
END;
/

The argument type is mandatory, but it is not the type checked by the Scheduler. So, if you are going to use it, you need to test the arguments definition before using it. Now that the program is complete, we can enable it.

--/
BEGIN
dbms_scheduler.enable('P_ARG01');
END;
/

We are reaching the goal of creating a job that passes an argument to the code. Everything is in place, so create the job now:

--/
BEGIN
sys.dbms_scheduler.create_job
(
job_name => 'TEST_ARG',
program_name => 'P_ARG01',
comments => 'test using a regular argument',
enabled => FALSE
);
END;
/

You might have noticed that the job is created in the disabled state. This is the same as for the program arguments—all of the job arguments have to be defined and given a value before the job can be enabled. Failing to do so will result in ORA-27457: argument 1 of job "MARVIN.TEST_ARG" has no value. We don't want such errors, so we define the arguments before trying to enable—and run—the job:

--/
BEGIN
dbms_scheduler.set_job_argument_value
(
job_name => 'TEST_ARG',
argument_name => 'V_ARG',
argument_value => 'manual'
);
END;
/

And finally we are ready to enable the job. The job will start immediately, so we can check the log table right away. First enable the job like this:

--/
BEGIN
dbms_scheduler.enable('TEST_ARG');
END;
/

If everything goes as expected, you will find another entry in the log table with "manual" in the job_name column. If you are experimenting with job arguments, you might notice that you don't need to disable a job to give its arguments a new value. As soon as you do, the job automatically becomes invalid. When you are ready, the job will not automatically get enabled again and you need to do so manually.

 

Summary


This was a quick glimpse of what the Scheduler can give us by using DB Console or Grid control. There is a lot more to say than this, as dbms_scheduler is full of surprises.

In this chapter we looked at:

  • What are the minimal privileges a user needs to create and run scripts

  • How to create a simple job that has only the PL/SQL code

  • How to create a simple job that calls a stored procedure

  • How to create a simple job that calls an executable

  • How to create a simple job that uses a program

  • How to assign metadata arguments to a program

  • How to assign normal application arguments to a program

  • How to use arguments in a job

In the next chapter, we will use the programs of this chapter to create a simple chain. Chains are really wonderful! You will love them. Here, you will see the real difference in dbms_job. Let's get us chained….

About the Author

  • Ronald Rood

    Ronald Rood has been an IT professional for over 20 years. His roots are in the Bull gcos8 mainframes where he played with assembly language until Oracle and UNIX came across his path. He eagerly joined the Oracle community and became a skilled innovating DBA and trouble shooter. Ronald's real power is in the combination of the rich UNIX world and Oracle. There is no such thing as a problem that can not be solved; it just might take a little time.

    Next to C, PRO*C, lots of scripting languages and – of course – PL/SQL, he also speaks Dutch, English, German, and some French. In his private time that he shares with his family with two children he likes to take an occasional dive (from the sky), fly radio controlled models, ride recumbent, and work as a volunteer for a local Water Scouts group.

    Currently Ronald is one of the Ciber oracle consultants in The Netherlands and cooperates in many projects for many large companies. Before writing this book Ronald wrote oracle related articles for the Dutch oracle user group magazine. On his blog you can find some short articles about various items, mostly oracle related.

    Ronald Rood has been interviewed by Oracle about Oracle Scheduler. Listen to it here here.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Very Good. Will come again
Book Title
Access this book, plus 7,500 other titles for FREE
Access now