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

Completed the Installing the Oracle Database 10g on Linux lesson

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.

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

Open a browser and enter the following URL:

http://<hostname>:5500/em

Login as system/<password> then click Login.

Click the Administration tab.

Select Users.

Select the HR user.

Select the System Privileges tab.

Click Modify.

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.

Click Apply.

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

Click Login.

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

Click the Administration tab.

You are now ready to create some jobs.

Select the Jobs link.

Click Create to create a new job.

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';

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

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

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

Select the Procedures link.

Click Create.

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;

Click your Database breadcrumb.

Select the Jobs link.

Click Create.

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

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

Select COALESCE_INDX001 from the list and click Select.

Click OK.

Select the Schedule tab.

Make sure Start is set to Immediately and click OK.

Click the Disabled tab.

Select ALTER_INDX_PROC001.

Click Yes for Enabled and click Apply.

Select the Scheduler Jobs breadcrumb.

Click the Run History tab.

Notice that your job ran successfully. Select ALTER_INDX_PROC001.

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

Select the Schedules link.

Click Create to create a new schedule.

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

Click the Database breadcrumb.

Select the Jobs link.

Click the Disabled tab.

Select ALTER_INDX001.

Click Yes for Enabled and then click the Schedule tab.

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

Select SCHED001 and click Select.

Click Apply.

Click the Scheduler Jobs breadcrumb.

Click on the Scheduled tab.

Click Refresh.

Your scheduled job appears.

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.

Click Create.

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

Enter the following information, then click OK.

Name: filename
Order: 1
Data Type: VARCHAR2

Your program has been created. Click your database breadcrumb.

Click Jobs.

Click Create.

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

Select Program Name and click the search light.

Select LOADDATA from the list and click Select.

Click OK.

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.

Your job has been submitted. Click Run History tab.

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

Click Login.

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.

Click Administration tab.

Click Users.

Enter OE and click Go.

Select OE.

Click System Privileges tab.

You need to add some privileges, click Modify.

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

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

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

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

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

Check LOADDATA and click Select.

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

Click Apply.

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

Click Login.

Login as OE/<password> and click Login.

Click Administration tab.

Click Jobs.

Click Create.

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

Select Program Name and click the search light.

Select LOADDATA from the list and click Select.

Click OK.

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.

Your job has been submitted. Click Run History tab.

Your job ran successfully using the same program.