MA NA GING AND M ONITORING THE OPERATIONA L DATABA SE in Font

Creator Code 3 of 9 in Font MA NA GING AND M ONITORING THE OPERATIONA L DATABA SE

3. You must set the size of the job array to the number of jobs you expect to create. testjobarr.extend(500); The statement shown here allocates space in the job array to hold information for 500 jobs. 4. The following code creates the 500 jobs and places them in the job array: for I in 1 . . . 500 loop testjob := sys.job(job_name => 'TESTJOB'||TO_CHAR(I), job_style => 'LIGHTWEIGHT', job_template => 'TEST_PROG', enabled => TRUE); testjobarr(i) := TESTJOB; end loop; The code shown here creates all 500 jobs using the TEST_PROG template. The jobs are added to the job array by the assignment operator testjobarr(i). 5. Use the CREATE_JOBS procedure to submit the array consisting of 500 jobs. dbms_scheduler.create_jobs (testjobarr, 'transactional'); The CREATE_JOBS procedure creates all 500 jobs at once. In this example, I chose to create lightweight jobs as part of the array, by specifying LIGHTWEIGHT as the value for the JOB_STYLE parameter when I created the job array. By not specifying the JOB_STYLE parameter, I can create a job array of regular database jobs instead of lightweight jobs. This is so, because the default value of the JOB_STYLE parameter is REGULAR.

External jobs are operating system executables that you run outside the database. You specify EXECUTABLE as the value for the JOB_TYPE parameter for an external job. If you use a named program for an external job, you must specify the complete directory path, for example, /usr/local/bin/perl, where you stored the executable, either in the JOB_ACTION attribute or the PROGRAM_ACTION attribute. You can create local external jobs and remote external jobs. A local external job runs on the same server as the job-originating database, and a remote external job runs on a remote host. You can use remote external jobs to manage jobs across your entire network from a single database. The interesting thing about remote external jobs is that you don t need to have an Oracle database instance running on the remote hosts. You ll just need to install a Scheduler Agent on each of the remote hosts where you wish to run external jobs, to accept job requests from the job-originating database, execute them on the remote host, and transmit the job results to the job-originating database. Running local external jobs is straightforward. All you need to do is to specify EXECUTABLE as the value for the JOB_TYPE or PROGRAM_TYPE arguments. To run remote external jobs, you ll need to install and configure the Scheduler Agent as well as assign credentials for executing the remote jobs. I explain the steps involved in setting up remote external jobs in the following sections.

You must set up the database from where you want to issue external job requests by doing the following:

1. Since you ll need the Oracle XML DB to run a remote external job, first check whether the Oracle XML DB option has been successfully installed in your database by issue the following DESCRIBE command: SQL> desc resource_view Name Null ----------------- ----RES XMLTYPE Type ---------------------------(XMLSchema "http://xm lns.oracle.com/xdb/XDBResour ce.xsd" Element "Resource") VARCHAR2(4000) RAW(16)

The DESCRIBE command shows that the Oracle XML DB option is correctly installed. If the query shows that Oracle XML DB isn t an option, you must install it before you can proceed. 2. Execute the Oracle-provided prvtsch.plb script, located in the $ORACLE_HOME/rdbms/admin directory. SQL> connect sys/sammyy1 as sysdba SQL> @$ORACLE_HOME/rdbms/admin/prvtrsch.plb PL/SQL procedure successfully completed. . . . PL/SQL procedure successfully completed. no rows selected Package created. Package body created. No errors. . . . User altered. SQL> 3. Finally, set a registration password for the Scheduler Agent. SQL> EXEC dbms_scheduler.set_agent_registration_pass( registration_password => 'sammyy1'.expiration_date => systimestamp + interval '7' day,max_uses => 25) PL/SQL procedure successfully completed. SQL> The Scheduler Agent uses the password to register with the database. The EXPIRATION_DATE and the MAX_USES parameters show the date when the password expires and the number of times the password can be used, respectively.