Open a browser and enter the following URL:

http://<your host name>:1158/em

Enter sys as the username, specify the password oracle, connect as SYSDBA, and then click Login.

Note: If you are accessing Enterprise Manager for the first time, then scroll down to the bottom of the page and click I agree.

Click on Administration tab.

Scroll down to Users and Privileges.

Under Users and Privileges, click Users.

Enter HR in the Object Name textbox and click Go.

Click Edit.

Click System Privileges.

Click Edit List.

Add the following privileges to the HR user. Select the following privileges from the Available System Privileges box. Click Move to move the privileges to Selected System Privileges box. Click OK.

CREATE EVALUATION CONTEXT
CREATE EXTERNAL JOB
CREATE JOB
CREATE PROCEDURE
CREATE RULE
CREATE RULE SET
SELECT ANY DICTIONARY

Click on Object Privileges.

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

Click the search light icon.

For Schema, select SYS from the drop down list and enter dbms_aq in Search Package Name. Click Go.

Select the DBMS_AQ and DBMS_AQADM check boxes and click Select.

In the Select Package Objects field after SYS.DBMS_AQADM, enter ",SYS.DBMS_LOCK", then select EXECUTE from the Available Privileges and move it to the Selected Privileges using the Move link. Click OK.

Click Apply.

Click Logout.

Open the window to view the Windows services. If you are using Windows XP, you follow this path:

Start > Control Panel > Administrative Tools > Services

Find the OracleJobSchedulerORCL service in the list of services. If the service is disabled, right-click the service and choose Properties..

The executable path for this service should point to your Oracle Home directory, and may differ from the directory shown here.

Change the Startup Type for the service to Manual.

Click Apply.

Click Start to start this service.

After the service has started, click OK.

Close the Services window and the Administrative Tools window.

Login to Enterprise Manager as hr. Click Login.

Enter hr as the username, hr as your password, and then click Login.

Note: If you are accessing Enterprise Manager for the first time, then scroll down to the bottom of the page and click I agree.

Click Administration tab.

Under Database Scheduler, select Programs.

Click Create.

Enter the following information, then click Add Another Row.

Name: progexe
Schema: HR
Enabled: Yes
Description: This is a program that runs an external executable
Type: EXECUTABLE
Executable Name: C:\WINDOWS\system32\cmd.exe

Enter the following argument information, then click Add Another Row. This argument instructs the cmd.exe program to exit when the batch script is finished.

Name: arg1
Default: /q

Enter the following argument information, then click Add Another Row. This argument turns off echoing for the cmd.exe program.

Name: arg2
Default: /c

Enter the following argument information, then click OK. This is the batch script that we want to execute.

Name: script_loc
Default: C:\wkdir\test1.bat

Your program has been created. Click the Database instance breadcrumb.

On the Administration page under Database Scheduler, select Jobs.

Click Create.

Enter the following information, then click Change Command Type.

Name: PROGEXE_JOB
Schema: HR
Enabled: Yes
Description: This job uses the program HR.PROGEXE
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 HR.PROGEXE from the list and click Select.

Click OK.

Click OK to submit the job.

Click the Running tab.

Your job is now running. You may need to click Refresh a couple of times until the job no longer appears in the list of running jobs. Then click the History tab.

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

Click Login.

Login as sys/oracle as SYSDBA and click Login.

Note: The user HR can grant the user OE access to the program progexe. You are logging in as SYS because you want to grant the user OE the CREATE EXTERNAL JOB, CREATE JOB and SELECT ANY DICTIONARY privileges at the same time and only a super user can perform that task.

Click Administration tab.

Scroll down to Users and Privileges.

Click Users.

Enter OE and click Go.

Select OE and click Edit.

Click System Privileges tab.

You need to grant additional privileges to this user. Click Edit List.

While holding down the Ctrl key, select CREATE EXTERNAL JOB, CREATE JOB and SELECT ANY DICTIONARY from the list of Available System Privileges. Release the Ctrl key and click Move. Then click OK.

Now you need to grant EXECUTE privileges on progexe program to the OE user. Click Object Privileges.

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

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

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

Select PROGEXE and click Select.

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

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

Enter SYS.DBMS_LOCK in the Select Package Objects box, 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/oe and click Login.

Note: If you are accessing Enterprise Manager for the first time as this user, then scroll down to the bottom of the page and click I agree.

Click Administration tab.

Under Database Scheduler, click Jobs.

Click Create.

Enter the following information, then click Change Command Type.

Name: PROGEXE_JOB2
Schema: OE
Enabled: Yes
Description: This job uses the program HR.PROGEXE
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 HR.PROGEXE from the list and click Select.

Click OK.

Click OK.

Your job has been submitted and is running. Click the History tab.

The job has completed successfully. Select Logout.

Login to Enterprise Manager as hr. Click Login.

Enter hr as the username, hr as your password, and then click Login.

Click Administration tab.

Under Database Scheduler, select Chains.

Click Create.

After entering the following information for the Job Chain:

Name: Simplechain
Schema: HR
Enabled: Yes
Description: A simple chain

Under Steps, enter Step1 for the Step name in the first row. Leave the Type as Program, then click the search light next to the Object Name field.

In the second row in the Steps section, enter the following information. Then click Create in the Rules section.

Step Name: Step2
Object Name: HR.PROGEXE

Create a rule to start the chain. Enter the following information, and click Continue.

Condition: true
Action: start step1

Scroll down and Click Create in the Rules section again.

In order to view the chain while it is running, delay the start of the second step by two and a half minutes. Enter the following information, and click Continue.

Condition: step1 succeeded
Action: after 00:02:30 start step2

Now create the rule that defines the condition to end the chain. Scroll down to the Rules section and click Create.

Enter the following information, and click Continue.

Condition: step2 completed
Action: end

Scroll down to see the steps and rules in your simple chain. Then click OK.

Your simple chain is created.

Click Create Job Using Chain.

Enter the following information and click OK.

Name: Chainjob
Description: Job using a Simple Chain

Chainjob has been created successfully. Click the Running tab to see the status of the job.

Click on Status icon to see the status of the steps running.

You see the state of each step in the chain. Click the Scheduler Jobs breadcrumb.

Click the All tab.

Click Refresh until you see that the job has successfully completed.

At the bottom of the page under Related Links, click Chains.

Create two programs to be used by the chain. Start SQL*Plus for Windows by clicking Start, selecting All Programs, selecting your Oracle Home installation, the Application Development group of programs, and then selecting SQL*Plus.

Enter hr for the User Name and Password, then click OK.

After you are connected, run the prog1 SQL script, which is located in the wkdir directory.

@C:\wkdir\prog1

This script contains the following SQL code:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'prog1',
program_type=>'PLSQL_BLOCK',
program_action=>'begin dbms_lock.sleep(90); end;',
enabled=>TRUE);
END;
/

Now execute the following script to create the second program.

@C:\wkdir\prog2

BEGIN
 DBMS_SCHEDULER.CREATE_PROGRAM(
 program_name=>'prog2',
 program_action=>'DECLARE
 num_tables NUMBER;

BEGIN
 SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
 IF num_tables < 1000 THEN
   raise_application_error(-20100, ''Expecting at least 1000 tables'');
 ELSE
   NULL;
 END IF;
 END;',
 program_type=>'PLSQL_BLOCK',
 number_of_arguments=>0,
 comments=>'',
 enabled=>TRUE);
END;

Minimize the SQL*Plus window, but do not close it. Switch to the Enterprise Manager window. In the Scheduler Chains screen, click Create.

Entering the following information under the Create Chain section:

Name: complexchain
Schema: HR
Enabled: Yes
Description: Create a complex chain

Under the steps section, enter the following information and then click Add 5 Steps.

In the sixth row, enter the following information.

Step Name: Step6
Type: Program
Object Name: HR.PROG1

Then scroll down to the Rules section and click Create.

Create a rule to start the chain. Enter the following information, and click Continue

Condition: true
Action: start step1

In the Rules section, click Create again.

Enter the following information, and click Continue

Condition: step1 succeeded
Action: start step2, step3

In the Rules section, click Create again.

Enter the following information, and click Continue.

Condition: step3 error_code=20100
Action: start step5

In the Rules section, click Create again.

Enter the following information, and click Continue.

Condition: step2 succeeded
Action: after 00:00:30 start step4

In the Rules section, click Create again.

Enter the following information, and click Continue.

Condition: step4 completed
Action: start step6

In the Rules section, click Create again.

Enter the following information, and click Continue.

Condition: (step5 completed and step6 completed) or step1 failed
Action: end

You have now created steps and rules for the complex chain. Click OK.

Your complex chain has been created successfully.

Select Complexchain and click Edit.

Enter the following information to add another step, then scroll down to the Rules section.

Step Name: Step7
Type: SUBCHAIN
Object Name: hr.simplechain

Click Create.

Enter the following information, and click Continue.

Condition: step2 succeeded
Action: start step7

Now you want to modify the rule that has the condition " step4 completed". Scroll down to the Rules section, select this rule, and click Edit.

Change the condition to step4 completed and step7 completed. Click Continue.

Scroll down to verify the new rule condition, then click Apply.

Your complex chain is modified successfully. Now you can schedule your chain. Click the Scheduler Chains breadcrumb.

Select COMPLEXCHAIN and click Create Job Using Chain.

Enter complexchainjob for the Name and enter Job using a complex chain in the description. Then click OK.

The complexchainjob job has been created successfully. Click the Running tab to see the status of the job.

Click on Status icon to see the status of the steps running.

Depending on when the page refreshes, you might see the status of the steps change. Click Reload in your browser to refresh the list. When all of the steps have executed, click the Scheduler Jobs breadcrumb.

Click the All tab.

Click Refresh.

The complexchainjob job ran successfully. Click the Database Instance breadcrumb.

Restore the SQL*Plus window, then run the following commands to add a directory.

CONNECT / AS SYSDBA
@C:\wkdir\add_dir.sql

create or replace directory file_arrival_directory as 'C:\wkdir';
grant read on directory file_arrival_directory to HR;

Switch back to Enterprise Manager. From the Administration tab, scroll down to the User Defined Types section and select Object Types.

Click Create.

Enter mytype for the name and under Attributes, click Add.

Enter file_name for the Name, select VARCHAR2 for the Type, and enter 80 for the Length. Then click OK.

Click OK to create the Object Type.

Your Object Type was created. Click your Database Instance breadcrumb.

Click the Maintenance tab.

Under Streams, Click Setup.

Click Messaging.

Click Create.

Select Normal Queue, Fixed Datatype and click Continue.

Enter event_queue for the Name and click Create New for the Queue Table.

Enter event_queue_table for the Name, under Payload Type, select Object Type and click the search light for Type.

Enter hr for Schema and click Go.

Select HR.MYTYPE and click Select.

Click OK.

Click Finish to create the messaging queue.

The queue was created. Click the Database Instance breadcrumb..

Click the Administration tab.

Click Jobs .

Click Create.

Enter file_detect for the Name and click Yes for Enabled. Copy the following script into the PL/SQL area, then click OK.

Declare
   FileDescriptor utl_file.file_type;
   my_msgid RAW(16);
   props dbms_aq.message_properties_t;
   enqopts dbms_aq.enqueue_options_t;
begin
LOOP
   BEGIN
   FileDescriptor := utl_file.fopen('FILE_ARRIVAL_DIRECTORY', 'file1', 'r');
 EXIT;
   EXCEPTION WHEN utl_file.INVALID_OPERATION THEN dbms_lock.sleep(1);
 END;
   END LOOP;
dbms_aq.enqueue(' event_queue', enqopts, props,
  MYTYPE('file1'), my_msgid);
  commit;
 end;       

Click Refresh until the job status shows 'RUNNING'.

Now change the schedule of Chainjob to be an event schedule. Select Chainjob and click Edit Job Definition.

Click Yes for enabled and then select the Schedule tab.

For Schedule Type, select Event, then click Change Queue.

Select HR.EVENT_QUEUE and click Select.

Specify a condtion that checks if an object in the queue has a value of 'file1' for the file_name attribute. To do this, enter tab.user_data.file_name=''file1'' in the Condition field, then click Apply.

Note: The word file1 has two single quotes on each end, not a single double quote character.

Chainjob has been successfully modified. Click the Scheduler Jobs breadcrumb.

Click Refresh. You now see that the Chainjob job status changes from SUCCEEDED to SCHEDULED.

The job does not execute until an event is placed in the HR.EVENT_QUEUE. To trigger the job execution, create a file named file1 in the C:\wkdir directory.

Open a command prompt window by clicking the Start button then selecting Run... In the Run window type cmd in the Open field then click OK.

Close the Run window, then switch back to Enterprise Manager. Click the Running tab.

Depend upon the speed of your system, EM displays either the file_detect job or the Chainjob in this window. Click Refresh until no more jobs are listed.

The job is finished. Click the History tab.

The file_detect and Chainjob jobs completed successfully. Click the Database Instance breadcrumb.

Restore the SQL*Plus window and run the following script to create schedules that represent each of the major holidays celebrated in the United States.

connect hr/hr
@C:\wkdir\holiday_schedule.sql

This script performs the following actions:

--Create US major holidays
 begin
   dbms_scheduler.create_schedule('Newyear', null,
   'FREQ=DAILY;BYDATE=0101', null, 'New Year');
end;
/

begin
   dbms_scheduler.create_schedule('MartinLutherKing', null,
   'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3MON',
   null, 'Martin Luther King Day');
end;
/

begin
   dbms_scheduler.create_schedule('PresidentDay', null,
   'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3MON',
   null, 'President Day');
end;
/

begin
   dbms_scheduler.create_schedule('MemorialDay', null,
   'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1MON',
   null, 'Memorial Day');
end;
/
begin
   dbms_scheduler.create_schedule('Independence', null,
   'FREQ=DAILY;BYDATE=0704',
   null, 'Independence Day');
end;
/

begin
   dbms_scheduler.create_schedule('LaborDay', null,
   'FREQ=MONTHLY;BYMONTH=SEP;BYDAY=1MON',
   null, 'Labor Day');
end;
/

begin
   dbms_scheduler.create_schedule('ColumbusDay', null,
   'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2MON',
   null, 'Columbus Day');
end;
/

begin
   dbms_scheduler.create_schedule
   ('VeteransDay', null, 'FREQ=MONTHLY;BYMONTH=NOV;BYMONTHDAY=11',
   null, 'Veterans Day');
end;
/
       

begin
   dbms_scheduler.create_schedule
    ('Thanksgiving', null, 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4THU',
     null, 'ThanksGiving');
end;
/

begin
   dbms_scheduler.create_schedule
   ('Xmas', null, 'FREQ=DAILY;BYDATE=1225', null, 'Christmas');
end;
/

In this step you create the following schedules:

	Holidays:           Combines all the schedules for holiday dates into one schedule
	Weekends:       A schedule of all weekend days
	Weekdays:       A schedule that consists of only the days Monday through Friday
	ClosedDays:     A schedule that combines weekend days with holidays
	WorkDays:        A schedule of all weekdays, excluding holidays
	LastWorkDay: The last work day of the month, excluding holidays

In the SQL*Plus window, run the following script:

@C:\wkdir\composite_schedule.sql

This script performs the following actions:

begin
   dbms_scheduler.create_schedule('Holidays', null,
   'Newyear,MartinLutherKing, PresidentDay,
   MemorialDay, Independence, LaborDay, ColumbusDay,
   VeteransDay, Thanksgiving, Xmas');
end;
/

-- Create Weekends schedule
begin
   dbms_scheduler.create_schedule 
   ('weekends',repeat_interval=> FREQ=DAILY;BYDAY=SAT,SUN');
end;
/

-- Create weekdays schedule
begin
   dbms_scheduler.create_schedule ('weekdays',
   repeat_interval=> 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI');
end;
/

-- Weekend and holiday schedule
begin
   dbms_scheduler.create_schedule ('ClosedDays',
   repeat_interval=> 'FREQ=DAILY;BYDAY=SAT,SUN;INCLUDE=holidays');
end;
/
    

-- Working Days
begin
   dbms_scheduler.create_schedule
    ('WorkDays', null,'WEEKDAYS; exclude= HOLIDAYS');
end;
/
    

-- Last work day of the month
begin
   dbms_scheduler.create_schedule
    ('LastWorkDay',null,'FREQ=MONTHLY;INCLUDE=WEEKDAYS;
     EXCLUDE=HOLIDAYS; BYSETPOS=-1');
end;
/

In this step you create a schedule that defines the fiscal quarters of a year (a user-defined frequency). You then use this schedule to create a schedule for the last working day in each quarter.

In the SQL*Plus window, run the following script:

@C:\wkdir\user_defined_freq.sql

This script performs the following actions:

-- Oracle's Fiscal year
begin
  dbms_scheduler.create_schedule(schedule_name => 'FISCALYEAR',
   start_date => to_timestamp_tz('01-JUN-2005 12:00:00','DD-MON-YYYYHH24:MI:SS'),
   repeat_interval => 'freq=yearly;bydate=0301,0601,0901,1201;periods=4');
end;
 /

-- Last workday in each quarter

begin
   dbms_scheduler.create_schedule
    ('LastQtrWorkDay',null,'FREQ=FISCALYEAR;INCLUDE=WorkDays;bysetpos=-1');
 end;
 /

Now you can review the schedules you just created by using Enterprise Manager. On the Administration page, under the heading Database Scheduler, click Schedules.

You see the schedules created by the scripts. Logout of Enterprise Manager, and then you can close the SQL*Plus window.