Tài liệu ORACLE8i- P24 docx

918 CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES

frequency than a day? Oracle allows you to do this by providing a way to define a

part of a day.

To specify part of a day, you need to divide the day into units. Thus, a day is 24

hours, 1440 minutes, or 86,400 seconds. Once you determine the unit of time that

you need to divide the day into, you determine what period of that increment you

want to represent. For example, if you want to represent an hour, that is 1/24 day. If

you want to represent a half hour, that is 1/48 day (because there are 48 half hours in

the day). Perhaps you want your job to run every 5 minutes. In this case, you should

use the value 5/1440. The 1440 represents the number of minutes in the day, and the

5 indicates that every 5 units of that 1440 increment (or every 5 minutes), the job will

execute. In the examples in Listings 20.1 and 20.2, the jobs are set up to run every

hour. Listing 20.3 shows an example of creating a job that will run every minute.

Listing 20.3: Running a Job at 1-Minute Intervals

BEGIN

DBMS_JOB.SUBMIT(job=>:job_num,

what=>’run_job;’,

next_date=>SYSDATE,

interval=>’SYSDATE + 1/1440’);

COMMIT;

END;

/

Modifying Jobs

With the DBMS_JOB.CHANGE procedure, you can change a variety of job informa￾tion, including what should be executed when the job is run, the interval of time

between each job execution, and the next time that the job should be executed. For

example, suppose that you want to modify job 6, which currently runs every hour, so

that it runs every minute. Listing 20.4 shows how to use the DBMS_JOB.CHANGE

procedure to change the interval.

Listing 20.4: Changing a Job Interval

BEGIN

DBMS_JOB.CHANGE(job=>6,what=>NULL, next_date=>NULL,

interval=>’SYSDATE + 1/1440’);

COMMIT;

END;

/

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

919

Notice the NULL values passed to the WHAT and NEXT_DATE parameters in this

example. Because there are no default values for these parameters, they must be given

some value when running the procedure. Since NULL values are passed to these param￾eters, no changes will be made to their settings.

As another example, suppose that you want to have the next job execution occur

in three days. Listing 20.5 shows how you could accomplish this modification.

Listing 20.5: Changing a Job’s Time of Execution

BEGIN

DBMS_JOB.CHANGE(job=>6,what=>NULL, next_date=>SYSDATE + 3,

interval=>NULL);

COMMIT;

END;

/

Suspending or Removing a Job

If you wish to suspend the execution of a job, use the DBMS_JOB.BROKEN procedure.

You can also use this procedure to restart the execution of a job. Here is an example of

breaking a job:

EXEC dbms_job.broken(6,TRUE);

In this case, job 6 will be broken after this statement is executed. The first parameter

is obviously the job number. The second parameter is a Boolean value (TRUE/FALSE)

that indicates if the job is broken (TRUE) or not (FALSE).

If you wish to unbreak a job, issue the following command:

EXEC dbms_job.broken(6,FALSE);

If you need to completely remove a job from the Job Scheduling facility, use the

DBMS_JOB.REMOVE procedure, as in this example:

EXEC dbms_job.remove(2);

This example removes job 2, which is a broken job. (You can see this in the query of

the DBA_JOBS view shown earlier, in the “Running a Job Once” section.)

Monitoring the Job Scheduler

The DBA_JOBS (and the USER_ and ALL_ varieties as well) data dictionary view pro￾vides a great deal of information about the various jobs in the job queue. The infor￾mation includes when the job last ran, when it is scheduled to run again, what the

job is, how many errors have occurred when the job has tried to run previously, and

SCHEDULING JOBS WITH THE JOB SCHEDULER

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

920 CHAPTER 20 • ORACLE8i SUPPLIED PACKAGES

how often the job actually runs. Table 20.2 describes the most commonly used

columns of the DBA_JOBS data dictionary view.

TABLE 20.2: COMMONLY USED COLUMNS IN THE DBA_JOBS VIEW

Column Description

JOB The job number assigned to the job

WHAT The actual job (PL/SQL routine) that will be executed

LOG_USER The user who submitted the job to the job queue

PRIV_USER The user whose privileges are ascribed to the job

SCHEMA_USER The default user who is used to parse the job (this is the schema that

should contain the job)

LAST_DATE The last date that the job was successfully run

THIS_DATE If the job is executing, this column will be populated; if the job is not exe￾cuting, this column will be NULL (can be used to determine how long a

job has been running)

NEXT_DATE The next execution date for the job

TOTAL_TIME The overall time that the system has spent on the job

BROKEN Indicates if the job is broken (which means it will not be scheduled for

execution)

FAILURES Indicates how many times this job has failed since its last success (16 suc￾cessive failures will cause the job to become broken)

Here is an example of querying the DBA_JOBS view and its results:

SELECT job, what, TO_CHAR(last_date,’mm/dd/yyyy hh24:mi:ss’) last_date,

TO_CHAR(next_date, ‘mm/dd/yyyy hh24:mi:ss’) next_date, failures, broken

FROM dba_jobs;

JOB WHAT LAST_DATE NEXT_DATE FAILURES B

--- ------------------------- ------------------- ------------------- -------- -

1 begin my_procedure; end; 06/16/2001 13:31:53 06/16/2001 13:36:53 0 N

The results show a procedure called MY_PROCEDURE that is scheduled to run. It

last ran on June 16 at 1:31 P.M., and it’s ready to go again at 1:36 P.M. on the same day.

No failures appear to have occurred with the execution of this job, and it’s not broken.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

921

Communicating through Pipes

The DBMS_PIPE package allows two or more sessions of an Oracle instance to com￾municate with each other. When the pipe is established, an area in the SGA is allo￾cated for that pipe. Other sessions can attach to that area of shared memory, and then

pass messages back and forth. Because all messaging is done through the SGA, be

aware that all messages sent through pipes will be lost once the instance is shut down.

NOTE Oracle’s Advanced Queuing feature provides for persistency of messages. See

Oracle’s documentation for information about Advanced Queuing.

The DBMS_PIPE package is very handy when you wish to perform real-time com￾munications between Oracle sessions. For example, you might want to monitor the

operation of multiple threads. You could establish a pipe between the running

processes and an application that you have written to monitor those processes. You

can also use a pipe for debugging triggers and stored PL/SQL code.

There are two different kinds of pipes in Oracle: public pipes and private pipes.

Let’s look at each of these types in a bit more detail.

Using Public Pipes

Public pipes are asynchronous in nature, and any user who has access to the pipe can

read or write to that pipe through the DBMS_PIPE package (as long as they have EXE￾CUTE privileges to the package). Once a user reads from the pipe, the message is

removed from the pipe. Therefore, there is a risk that the user you intended the mes￾sage for in the public pipe will never receive that message.

Public pipes can be either created implicitly or explicitly. An implicit public pipe is

created automatically the first time it is referenced, and no specific creation procedure

is required. An explicit public pipe is created by calling the CREATE_PIPE function

and setting the private pipe type flag to N. You must de-allocate an explicitly created

pipe with the REMOVE_PIPE function.

Using Private Pipes

Access to private pipes is much more restricted than access to implicit public pipes.

Any session that wishes to connect to the private pipe created by your session must be

logged in using the same user ID as the session that created the pipe: SYSDBA or

COMMUNICATING THROUGH PIPES

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com