Administering Jobs Oracle 10g

A job is the combination of a schedule and a program, along with any additional arguments required by the program. This section introduces you to basic job tasks, and discusses the following topics:

  • Job Tasks and Their Procedures
  • Creating Jobs
  • Copying Jobs
  • Altering Jobs
  • Running Jobs
  • Stopping Jobs
  • Dropping Jobs
  • Disabling Jobs
  • Enabling Jobs

Job Tasks and Their Procedures

We illustrates common job tasks and their appropriate procedures and privileges:

Job Tasks and Their Procedures

Job Tasks and Their Procedures

Creating Jobs

You create jobs using the CREATE _JOB procedure. When creating a job, you must specify the action of the job, the schedule for the job, as well as some other attributes of the job. For example, the following statement creates a job called my _ emp _job1, which is an insert into the sales table:

You can create a job in another schema by specifying schema.job_name. The creator of a job is, therefore, not necessarily the job owner. The job owner is the user in whose schema the job is created, while the job creator is the user who is creating the job. Jobs are executed with the privileges of the schema in which the job is created. The NLS environment of the job when it runs is that which was present at the time the job was created.

Once a job is created, it can be queried using the *_SCHEDULER _JOBS views. Jobs are created disabled by default and they need to be enabled in order to be executed.

Job Attributes

Some job attributes are set at job creation time, while other job attributes are not. Instead, you can specify these attributes after the job has been created by using the SET _ATTRIBUTE procedure. See PL/SQL Packages and Types Reference for information about the SET _ATTRIBUTE procedure.

Setting Job Arguments

After creating a job, you may need to set job arguments. To set job arguments, use the SET _JOB _ARGUMENT _VALUE or SET _JOB _ANYDATA _VALUE procedures. Both Administering Jobs procedures have the same purpose, but SET _JOB _ANYDATA _VALUE is used for types that cannot be implicitly converted to and from VARCHAR2. A typical situation where you might want to set a job argument is for adding a new employee to a department. In this case, you might have a job that adds employees and assigns them the next available number in the department for a department ID. The following statement does this:

If you use this procedure on an argument whose value has already been set, it will be overwritten. You can set argument values either by using the argument name or by the argument position. If a program is inlined, only setting by position is supported. Arguments are not supported for jobs of type plsql _block.

To remove a value that has been set, us the RESET _JOB _ARGUMENT procedure. This procedure can be used for both regular and anydata arguments. See PL/SQL Packages and Types Reference for information about the procedures used in setting job arguments and their syntax.

Ways of Creating Jobs

You create a job using the CREATE _JOB procedure. Because this procedure is overloaded, there are several different ways of using it. In addition to inlining a job during the job creation, you can also create a job that points to a saved program and schedule. This is discussed in the following sections:

  • Creating Jobs Using a Saved Program
  • Creating Jobs Using a Saved Schedule
  • Creating Jobs Using a Saved Program and Schedule

Creating Jobs Using a Saved Program You can also create a job by pointing to a saved program instead of inlining its action. To create a job using a saved program, you specify the value for program _name in the CREATE _JOB procedure when creating the job and do not specify the values for job _type, job _action, and number _of _arguments.

To use an existing program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privileges on it. An example of using the CREATE _JOB procedure with a saved program is the following statement, which creates a job called my _new _job1:

Creating Jobs Using a Saved Schedule You can also create a job by pointing to a saved schedule instead of inlining its schedule. To create a job using a saved schedule, you specify the value for schedule _name in the CREATE _JOB procedure when creating the job and do not specify the values for start _date, repeat _interval, and end_date.

You can use any saved schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a saved schedule is the following statement, which creates a job called my_new_ job2:

Creating Jobs Using a Saved Program and Schedule A job can also be created by pointing to both a saved program and schedule. An example of using the CREATE _JOB procedure with a saved program and schedule is the following statement, which creates a new job called my _new _job3 based on the existing program my _saved_ program1 and the existing schedule my _saved _schedule1:

Copying Jobs

You copy a job using the COPY_JOB procedure. This call copies all the attributes of the old job to the new job except the new job is created disabled and has another name.

Privileges Required for Copying a Job

You can copy a job if you are the owner of the job, or have ALTER privileges on the job, or have the CREATE ANY JOB privilege. Only SYS can copy a job from or into the SYS schema.

Altering Jobs

You alter a job using the SET_ATTRIBUTE procedure. All jobs can be altered, and, with the exception of the job name, all job attributes can be changed. If an enabled job is altered, the Scheduler will disable it, make the change and then reenable it. If any errors are encountered during the enable process, the job is not enabled and an error is generated. If there is a running instance of the job when the SET _ATTRIBUTE call is made, it is not affected by the call. The change is only seen in future runs of the job.

If any of the schedule attributes of a job are altered while the job is running, the next job run will be scheduled using the new schedule attributes. Schedule attributes of a job include schedule _name, start_date, end_date, and repeat _interval.

If any of the program attributes of a job are altered while the job is running, the new program attributes will take effect the next time the job runs. Program attributes of a job include program _name, job _action, job_type, and number _of_ arguments. This is also the case for job argument values that have been set.

Granting ALTER on a job will let a user alter all attributes of that job except its program attributes (program_name, job_type, job_action program _action, and number _of _arguments) and will not allow a user to use a PL/SQL expression to specify the schedule for a job.

In general, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in several views. The attributes of a job are available in the *_SCHEDULER _ JOB views.

It is perfectly valid for running jobs to alter their own job attributes using the SET_ ATTRIBUTE procedure, however, these changes will not be picked up until the next scheduled run of the job.

See PL/SQL Packages and Types Reference for detailed information about the SET _ATTRIBUTE procedure and "Configuring the Scheduler".

Running Jobs

Normally, jobs are executed asynchronously. The user creates a job and the API immediately returns and indicates whether the creation was successful. To find out whether the job succeeded, the user has to query the job table or the job log. While this is the expected behavior, for special cases, the database also allows users to execute jobs synchronously.

Running Jobs Asynchronously

You can schedule a job to run asynchronously based on the schedule defined when the job is created. In this case, the job is submitted to the job coordinator and is picked up by the job slaves for execution.

Running Jobs Synchronously

Once a job has been created, you can run the job synchronously using the RUN _JOB procedure with the use _current _session argument set to TRUE. In this case, the job will run within the user session that invoked the RUN _JOB call instead of being picked up by the coordinator and being executed by a job slave. To run the job using the RUN_JOB procedure, it must be enabled.

You can use the RUN _JOB procedure to test a job, thereby ensuring that it runs without errors. It can also be used to run a job outside of its specified schedule. For example, if an instance of a job failed because of some error. Once you fix the errors, you can use this procedure to run the job instead of scheduling a separate job for it. Running a job using the RUN _JOB procedure with its use _current _session argument set to TRUE does not change the count for failure _count and run_ count for the job. The job run will, however, be reflected in the job log. Runtime errors generated by the job are passed back to the invoker of RUN_JOB.

Job Run Environment

Jobs are run with the privileges that are granted to the job owner directly or indirectly through default logon roles. External OS roles are not supported. Given sufficient privileges, users can create jobs in other users' schemas. The creator and the owner of the job can, therefore, be different. For example, if user jim has the CREATE ANY JOB privilege and creates a job in the scott schema, then the job will run with the privileges of scott.

The NLS environment of the session in which the job was created are saved and is used when the job is being executed. To alter the NLS environment in which a job runs, a job must be created in a session with different NLS settings.

Running External Jobs

An external job is a job that runs outside the database. All external jobs run as a low -privileged guest user, as has been determined by the database administrator while configuring external job support. Because the executable will be run as a low -privileged guest account, you should verify that it has access to necessary files and resources. Most, but not all, platforms support external jobs. For platforms that do not support external jobs, creating or setting the attribute of a job or a program to type EXECUTABLE returns an error. See your operating system -specific documentation for more information.

For an external job, job _type is specified as EXECUTABLE (If using named programs, the corresponding program _type would be EXECUTABLE). job_ action (or corresponding program _action if using named programs) is the full OS-dependent path of the desired external executable plus optionally any command line arguments. For example, /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for type EXECUTABLE must be a string type such as CHAR, VARCHAR2, or VARCHAR.

Some additional post -installation steps might be required to ensure that external jobs run as a low -privileged guest user. See your operating system -specific documentation for any post -installation configuration steps.

Setting Environment Variables for External Jobs

To ensure that environment variables can be used with external jobs, you can use a wrapper such as Perl or sh before invoking the external job. As an example, if you have an external job (hello.exe) with USER_NAME and LOCATION environment variables, you could create a perl wrapper (hello.pl) such as the following:

With a program_action of C:\home\hello.exe, the values would be C:\perl\ bin\ perl.exe (or /usr/local/bin/perl on UNIX, or /home/mydir/bin/hello.pl, if hello .pl is executable). Your program arguments would be the following:

"C:\home\hello.exe" --- the path where hello.exe is located"Myname" --- the value for USER_NAME"Mytown" --- the value for LOCATION

Stopping Jobs

You stop a running job using the STOP_JOB procedure. Job classes reside in the SYS schema, therefore, whenever job classes are used in comma -delimited lists, they must be preceded by SYS. For example, the following statement stops job1:

Any instance of the job will be stopped. After stopping the job, the state of a one-time job will be set to STOPPED whereas the state of a repeating job will be set to SCHEDULED because the next run of the job is scheduled.

The Scheduler tries to gracefully stop the job using an interrupt mechanism. This method gives control back to the slave process, which can update the status of the job to STOPPED.

If the interrupt is not successful, the STOP _JOB call will fail. The job will be stopped as soon as possible after its current uninterruptable operation is done. Users with the MANAGE SCHEDULER privilege can force the job to stop sooner by setting the force option to TRUE in the STOP _JOB call. In this case, the call forcibly terminates the slave process that was running the job, thus stopping the job.

The STOP_JOB procedure accepts job_name as an argument. This can be the name of a job or a comma -delimited list of job names. It can also be the name of a job class or a list of job class names. For example, the following statement combines both jobs and job classes:

If the name of a job class is specified using STOP _JOB, the jobs that belong to that job class are stopped. The job class is not affected by this call.

Only running jobs can be stopped. Stopping a job that is not running generates a PL/SQL exception saying that the job is not running. Stopping a job that does not exist also causes an error. When a list of job names is provided, the Scheduler stops executing the list of jobs on the very first job that returns an error.

Dropping Jobs

You drop a job using the DROP _JOB procedure. Dropping a job results in the job being removed from the job table, its metadata being removed, and it no longer being visible in the *_SCHEDULER _JOBS views. Therefore, no more runs of the job will be executed.

If an instance of the job is running at the time of the call, the call results in an error. You can still drop the job by setting the force option in the call to TRUE. Setting the force option to TRUE attempts to first stop (issues the STOP_JOB call) the running job instance and then drop the job. By default, force is set to FALSE. If the user does not have privileges to stop the job, the DROP_JOB call will fail.

The DROP _JOB procedure accepts job_name as an argument. This can be the name of a job or a comma -delimited list of job names. It can also be the name of a job class or a list of job class names. For example, the following statement combines both jobs and job classes:

If the name of a job class is specified in this procedure call, the jobs that belong to that job class are dropped, but the job class itself is not dropped. The DROP _JOB _ CLASS procedure should be used to drop the job class.

Attempting to drop a job or job class that does not exist generates an error stating that the object does not exist. If a list of job names is specified in the DROP _JOB call, the call fails on the first job that cannot be dropped. In the preceding example, if job2 could not be dropped, the DROP _JOB call fails. job1 will be dropped but it will not be attempted to drop the other jobs in the list. The error returned by the Scheduler will contain the name of the job that caused the error.

Disabling Jobs

You disable a job using the DISABLE procedure. A job can also become disabled for other reasons. For example, a job will be disabled when the job class it belongs to is dropped. A job is also disabled if either the program or the schedule that it points to is dropped. Note that if the program or schedule that the job points to is disabled, the job will not be disabled and will therefore result in an error when the Scheduler tries to execute the job.

Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state in the job table is changed to disabled. When a job is disabled with the force option set to FALSE and the job is currently running, an error is returned. When force is set to TRUE, the job is disabled, but the currently running instance is allowed to finish.

You can also disable several jobs in one call by providing a comma -delimited list of job names or job class names to the DISABLE procedure call. For example, the following statement combines jobs with job classes:

Note that if a list of job class names is provided, the jobs in the job class are disabled.

Note that if it is not possible to disable job2, then the DISABLE call will fail. job1 will be disabled but job2, job3, and jobs in jobclass1, and jobclass2 will not be disabled.

Enabling Jobs

You enable jobs by using the ENABLE procedure. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

You can enable several jobs in one call by providing a comma -delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

Note that if a list of job class names is provided, the jobs in the job class are enabled. Also, if it is not possible to enable job2, then the ENABLE call will fail. job1 will be enabled but job2, job3, jobclass1, and jobclass2 will not be enabled.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics