0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } function MM_openBrWindow(theURL,winName,features) { //v2.0 window.open(theURL,winName,features); } //-->

OBE Home > Database 10 g > Single Instance > Manageability > Jobs

Scheduling Jobs

Purpose

This module describes how you can use the Database Scheduler to automate jobs.

Topics

This module will discuss the following topics:

 Overview
 Prerequisites
 Creating a Job that Executes a PL/SQL Block
 Creating a Job that Executes a PL/SQL Procedure
 Creating Saved Schedules
 Editing a Job to Use a Saved Schedule
 Creating Jobs Using Programs

Viewing Screenshots

 Place the cursor on this icon to display all screenshots. You can also place the cursor on each icon to see only the screenshot associated with it.

Overview

Back to List

What is the Database Scheduler?

The Scheduler enables database administrators and application developers to control when and where various tasks take place. These tasks can be time consuming and complicated, so using the Scheduler can help you to improve the management and planning of these tasks. In addition, by ensuring that many routine database tasks occur without manual intervention, you can lower operating costs, implement more reliable routines, and minimize human error.

The Scheduler uses three main components:

 A schedule specifies when and how many times a job is executed. Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.
 A program is a collection of metadata about what will be run by the scheduler. This includes information such as the program name, the type of program, and information about arguments passed to the program.
 A job specifies what needs to executed and when. For example, the "what" could be a PL/SQL procedure, an executable C program, a java application, a shell script, or client-side PL/SQL. You can specify the program (what) and schedule (when) as part of the job definition, or you can use an existing program or schedule instead.

Prerequisites

Back to Topic List

Before starting this module, you should have:

1.

Completed the Configuring Linux for the Installation of Oracle Database 10g lesson

2.

Completed the Installing the Oracle Database 10g on Linux lesson

3.

Download and unzip the scheduler.zip files into your working directory (i.e. /home/oracle/wkdir). Update the loaddata1.sh file with your environment variables and path settings. In addition, the permissions on these files must be set so an OS user like nobody can access the files. Note: the files provided are for UNIX/Linux and must be modified for windows environment.

4.

Completed the Setting Privileges for the HR User section in this lesson

Setting Privileges for the HR User

Back to Topic List

This task is performed by the administrator. Before the user HR can create jobs, you need to assign some privileges to HR. Perform the following:

1.

Open a browser and enter the following URL:

                               
                                 
http://<hostname>:5500/em
                              
                            

Login as system/<password> then click Login.

Move your mouse over this icon to see the image

 

2.

Click the Administration tab.

Move your mouse over this icon to see the image

 

3.

Select Users.

Move your mouse over this icon to see the image

 

4.

Select the HR user.

Move your mouse over this icon to see the image

 

5.

Select the System Privileges tab.

Move your mouse over this icon to see the image

 

6.

Click Modify.

Move your mouse over this icon to see the image

 

7.

Select the privileges CREATE JOB and SELECT ANY DICTIONARY and click Move. Then click OK. Normally, the MANAGE SCHEDULER system privilege does not need to be granted to users. However, HR will need this system privilege to be able to force open a window later on in the lesson.

Move your mouse over this icon to see the image

 

8.

Click Apply.

Move your mouse over this icon to see the image

 

9.

You can now logoff as SYSTEM and log on as the user HR. Click Logout.

Move your mouse over this icon to see the image

 

10.

Click Login.

Move your mouse over this icon to see the image

 

11.

Enter hr/<password> then click Login. You may receive a Licensing window. If you do, scroll down and click I Agree.

Move your mouse over this icon to see the image

 

12.

Click the Administration tab.

Move your mouse over this icon to see the image

You are now ready to create some jobs.

 

Creating a Job that Executes a PL/SQL Block

Back to Topic List

You will first create a simple, self-contained job. This means that all the job attributes will be specified in the job itself. Perform the following:

1.

Select the Jobs link.

Move your mouse over this icon to see the image

 

2.

Click Create to create a new job.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click OK.

Name: ALTER_INDX001
Owner: HR
Enabled: Yes
Description: This job will coalesce index HR.EMP_NAME_IX on the EMPLOYEES table.
Logging Level: log job runs only (RUNS)
Job Class: DEFAULT_JOB_CLASS
Auto Drop: FALSE
Restartable: TRUE
Command: EXECUTE IMMEDIATE 'alter index HR.EMP_NAME_IX coalesce';

Move your mouse over this icon to see the image

 

4.

Your job was executed. Click the Run History tab. If you do not see your job, click Refresh.

Move your mouse over this icon to see the image

 

5.

You see the job ALTER_INDX001 completed successfully. Click on the job to see the job execution details.

Move your mouse over this icon to see the image

 

6.

Now you are ready to create a job that executes a PL/SQL procedure. Click the database breadcrumb at the top of the window.

Move your mouse over this icon to see the image

 

Creating a Job that Executes a PL/SQL Procedure

Back to Topic List

In this case, you will first create a PL/SQL procedure and then create a job that will point to the procedure which will execute when the job is executed. You will perform the following:

 Creating a Procedure
 Create a Job that uses the PL/SQL Procedure

Creating a Procedure

Back to List

Perform the following:

1.

Select the Procedures link.

Move your mouse over this icon to see the image

 

2.

Click Create.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click OK.

Name: COALESCE_INDX001
Schema: HR
Source:
as
begin
EXECUTE IMMEDIATE 'alter index HR.EMP_NAME_IX coalesce';
end;

Move your mouse over this icon to see the image

 

4.

Click your Database breadcrumb.

Move your mouse over this icon to see the image

 

Create a Job that uses the PL/SQL Procedure

Back to List

Now you can create the job. Perform the following:

1.

Select the Jobs link.

Move your mouse over this icon to see the image

 

2.

Click Create.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click Change Command Type.

Name: ALTER_INDX_PROC001
Owner: HR
Enabled: No
Description: This job will coalesce index HR.EMP_NAME_IX on the EMPLOYEES table.
Logging Level: log job runs only (RUNS)
Job Class: DEFAULT_JOB_CLASS
Auto Drop: FALSE
Restartable: TRUE

Move your mouse over this icon to see the image

 

4.

Select In-line Program: Stored Procedure and click the search light.

Move your mouse over this icon to see the image

 

5.

Select COALESCE_INDX001 from the list and click Select.

Move your mouse over this icon to see the image

 

6.

Click OK.

Move your mouse over this icon to see the image

 

7.

Select the Schedule tab.

Move your mouse over this icon to see the image

 

8.

Make sure Start is set to Immediately and click OK.

Move your mouse over this icon to see the image

 

9.

Click the Disabled tab.

Move your mouse over this icon to see the image

 

10.

Select ALTER_INDX_PROC001.

Move your mouse over this icon to see the image

 

11.

Click Yes for Enabled and click Apply.

Move your mouse over this icon to see the image

 

12.

Select the Scheduler Jobs breadcrumb.

Move your mouse over this icon to see the image

 

13.

Click the Run History tab.

Move your mouse over this icon to see the image

 

14.

Notice that your job ran successfully. Select ALTER_INDX_PROC001.

Move your mouse over this icon to see the image

 

15.

You may view the logged history of the job. When done, click your Database breadcrumb.

Move your mouse over this icon to see the image

 

Creating a Saved Schedule

Back to Topic List

At this point you want to create a schedule that will run at 11:00PM every night for the next year. Perform the following:

1.

Select the Schedules link.

Move your mouse over this icon to see the image

 

2.

Click Create to create a new schedule.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click OK.

Name: SCHED001
Owner: HR
Description: Run at 11:00PM every night for the next year
Start: Later
Date: Today's date
Time: 11:00PM
Frequency: 1 Days
Repeat Until: Custom
Date: One year from today's date
Time 11:00PM

Move your mouse over this icon to see the image

 

4.

Click the Database breadcrumb.

Move your mouse over this icon to see the image

 

Editing a Job to Use a Saved Schedule

Back to Topic List

You would like to associated the saved schedule you just created, to a job. Perform the following:

1.

Select the Jobs link.

Move your mouse over this icon to see the image

 

2.

Click the Disabled tab.

Move your mouse over this icon to see the image

 

3.

Select ALTER_INDX001.

Move your mouse over this icon to see the image

 

4.

Click Yes for Enabled and then click the Schedule tab.

Move your mouse over this icon to see the image

 

5.

Select the Schedule Type Use Pre-defined Schedule, then click on the search light to select a schedule.

Move your mouse over this icon to see the image

 

6.

Select SCHED001 and click Select.

Move your mouse over this icon to see the image

 

7.

Click Apply.

Move your mouse over this icon to see the image

 

8.

Click the Scheduler Jobs breadcrumb.

Move your mouse over this icon to see the image

 

9.

Click on the Scheduled tab.

Move your mouse over this icon to see the image

 

10.

Click Refresh.

Move your mouse over this icon to see the image

 

11.

Your scheduled job appears.

Move your mouse over this icon to see the image

 

Creating Jobs Using Programs

Back to Topic List

Programs are used to provide a level of abstration. The action is specified in the program and not the job itself. The jobs then point to the program. If, for example, the shell script name or location changes, each job does not have to be changed, only one program. In this section, you will create a program and two jobs that each point to the program. You will perform the following:

 Creating a Program
 Creating a Job that Uses the Program
 Sharing the Program with Another User
 Creating a Job that Resides in Another Schema that uses the Program

Creating a Program

Back to List

Perform the following:

1.

Make sure you have copied the files from the prerequisites for this lesson and placed them in your working directory. From the Administration tab, click Programs.

Move your mouse over this icon to see the image

 

2.

Click Create.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click Add Another Row .

Name: LOADDATA
Schema: HR
Enabled: Yes
Description: This program calls the loaddata1.sh shell script
Type: EXECUTABLE
Execution Name: /<wkdir path>/loaddata1.sh

Move your mouse over this icon to see the image

 

4.

Enter the following information, then click OK.

Name: filename
Order: 1
Data Type: VARCHAR2

Move your mouse over this icon to see the image

 

5.

Your program has been created. Click your database breadcrumb.

Move your mouse over this icon to see the image

 

Creating a Job that Uses the Program

Back to List

Now you will create a job that uses the program you just created. Perform the following:

1.

Click Jobs.

Move your mouse over this icon to see the image

 

2.

Click Create.

Move your mouse over this icon to see the image

 

3.

Enter the following information, then click Change Command Type.

Name: LOADDATA_JOB1
Schema: HR
Enabled: Yes
Description: This job uses the program loaddata
Logging Level: log job runs only (RUNS)
Job Class: DEFAULT_JOB_CLASS
Auto Drop: FALSE
Restartable: TRUE

Move your mouse over this icon to see the image

 

4.

Select Program Name and click the search light.

Move your mouse over this icon to see the image

 

5.

Select LOADDATA from the list and click Select.

Move your mouse over this icon to see the image

 

6.

Click OK.

Move your mouse over this icon to see the image

 

7.

Scroll down to the Arguments heading.

Move your mouse over this icon to see the image

 

8.

Select User defined from the drop down list for the Option column. Enter /<wkdir path>/loaddata1.dat in the Value column and click OK.

Move your mouse over this icon to see the image

 

9.

Your job has been submitted. Click Run History tab.

Move your mouse over this icon to see the image

 

10.

Your job was run successfully. The next section requires you to be logged in as a super user. Click Logout.

Move your mouse over this icon to see the image

 

Sharing the Program with Another User

Back to List

In order for another user to use the program, the user who created the program or the super user needs to share it. Perform the following:

1.

Click Login.

Move your mouse over this icon to see the image

 

2.

Login as system/<password> and click Login. Note: the user HR can grant the user OE access to the program loaddata. You are login as system because you also want to grant the user OE the CREATE JOB privilege at the same time and only a super user can perform that task.

Move your mouse over this icon to see the image

 

3.

Click Administration tab.

Move your mouse over this icon to see the image

 

4.

Click Users.

Move your mouse over this icon to see the image

 

5.

Enter OE and click Go.

Move your mouse over this icon to see the image

 

6.

Select OE.

Move your mouse over this icon to see the image

 

7.

Click System Privileges tab.

Move your mouse over this icon to see the image

 

8.

You need to add some privileges, click Modify.

Move your mouse over this icon to see the image

 

9.

Select CREATE JOB and SELECT ANY DIRECTORY from the list and click Move. Then click OK.

Move your mouse over this icon to see the image

 

10.

Now you need to add EXECUTE privilege for the LOADDATA program you created previously. Click Object Privileges.

Move your mouse over this icon to see the image

 

11.

Select Programs from the Select Object Type drop down list and click Add.

Move your mouse over this icon to see the image

 

12.

Select the Search light next to the Select Programs Objects box.

Move your mouse over this icon to see the image

 

13.

Select HR from the Schema drop down list box and click Go.

Move your mouse over this icon to see the image

 

14.

Check LOADDATA and click Select.

Move your mouse over this icon to see the image

 

15.

Select EXECUTE from the list of privileges and click Move. Then click OK.

Move your mouse over this icon to see the image

 

16.

Click Apply.

Move your mouse over this icon to see the image

 

17.

The user OE has been updated. You now need to login as OE. Click Logout.

Move your mouse over this icon to see the image

 

Creating a Job that Resides in Another Schema that uses the Program

Back to List

In order for another user to use the program, the super user needs to give them access. Perform the following:

1.

Click Login.

Move your mouse over this icon to see the image

 

2.

Login as OE /<password> and click Login.

Move your mouse over this icon to see the image

 

3.

Click Administration tab.

Move your mouse over this icon to see the image

 

4.

Click Jobs.

Move your mouse over this icon to see the image

 

5.

Click Create.

Move your mouse over this icon to see the image

 

6.

Enter the following information, then click Change Command Type.

Name: LOADDATA_JOB2
Schema: OE
Enabled: Yes
Description: This job uses the program loaddata
Logging Level: log job runs only (RUNS)
Job Class: DEFAULT_JOB_CLASS
Auto Drop: FALSE
Restartable: TRUE

Move your mouse over this icon to see the image

 

7.

Select Program Name and click the search light.

Move your mouse over this icon to see the image

 

8.

Select LOADDATA from the list and click Select.

Move your mouse over this icon to see the image

 

9.

Click OK.

Move your mouse over this icon to see the image

 

10.

Scroll down to the Arguments heading. Select User defined from the drop down list for the Option column. Enter /<wkdir path>/loaddata1.dat in the Value column and click OK.

Move your mouse over this icon to see the image

 

11.

Your job has been submitted. Click Run History tab.

Move your mouse over this icon to see the image

 

12.

Your job ran successfully using the same program.

Move your mouse over this icon to see the image

 

 Place the cursor on this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve