Table 19.3. Special job definition parameters.

Some examples of the job definition when submitting a job are

`HELLO;'
`paycheck(`'FRIDAY'',SYSDATE);'
`sample(`'String1'',100,20,''String2'');'
`final_try(`'good'',next_date_broken);'
`dbms_job.remove(job);'

Viewing Jobs

To view any information about jobs, you can use queries. The three views that display information about jobs in the job queue are shown in Table 19.4.

Table 19.4. Data dictionary views for jobs.

The view for USER_JOBS and DBA_JOBS has the same structure. At any point in time, you could type

INPUT
SELECT * from USER_JOBS;

The preceding command enables you to view all the possible columns. See Table 19.5 for some of the possible columns and meanings.

Table 19.5. Columns used in views DBA_JOBS and DBA_USERS.

To see all the available columns (and all jobs currently running), you would type

INPUT
SELECT * from DBA_JOBS_RUNNING;

The available columns and descriptions are shown in Table 19.6.

Table 19.6. Columns used in view DBA_JOBS_RUNNING.

Samples for Viewing Jobs

This section gives some more examples of how to view jobs. The first example in Listing 19.7 will display all the jobs that are currently executing.

INPUTListing 19.7. Viewing executing jobs.

SELECT SID,JOB,THIS_SEC,FAILURES from DBA_JOBS_RUNNING;

If you want to view information on jobs that you own, use the code in Listing 19.8 to view the process name, job number, and the next date the job will execute.

INPUTListing 19.8. Viewing your own jobs.

SELECT JOB,WHAT,NEXT_DATE,FAILURES,BROKEN from USER_JOBS;

Job Management

So far, you have focused on creating and viewing jobs. Now, you will learn about a major responsibility of a systems administrator—performing job management. Job management can include removing a job, altering a job, importing and exporting jobs from one database to another, and even fixing broken jobs. You might also need to manage how long a job runs; if a job is taking too long, you might have to review the procedure and either fine-tune it or delegate this task to another Oracle expert.

Removing a Job

If you can submit jobs, you should be able to remove them. Oracle provides the REMOVE procedure, which enables you to remove only jobs that you own.

The Syntax for the REMOVE Procedure
The format for the procedure REMOVE is

PROCEDURE REMOVE(job_number IN BINARY_INTEGER);

Listing 19.9 shows an example of removing a job assigned the number 109.

INPUTListing 19.9. Removing a job.

1: BEGIN
2:      DBMS_JOBS.REMOVE(109);
3: END;

ANALYSIS After you execute the code, if the job isn't running, job 109 will be removed permanently from the job queue.

Altering a Job

After a job has been submitted, you can change its parameters with the CHANGE procedure. If you want to alter specific parameters of the job, you would use WHAT, NEXT_DATE, or INTERVAL. Again, you can only change jobs that you own; otherwise there would be utter chaos!

The Syntax for Changing Job Parameters
Several possible formats can be used to change job parameters. The first uses the CHANGE procedure:

PROCEDURE CHANGE(job_number IN BINARY_INTEGER,
                             process_name IN VARCHAR2,
                             next_run IN DATE,
                             interval IN VARCHAR2 );

The second option uses the WHAT procedure:

PROCEDURE WHAT(job_number in BINARY_INTEGER,
                             process_name IN VARCHAR2);