Altering Queued Jobs

The CHANGE, WHAT, NEXT_DATE, INTERVAL, and BROKEN procedures are used to alter the state of a job. Following are a couple of examples.

To designate the job as a broken job:

SQL> begin

  2  dbms_job.broken(1, TRUE);

  3 end;

  4 /



PL/SQL procedure successfully completed.

---

NOTE:  
A broken job cannot be run unless it is unmarked “broken” using the BROKEN procedure or forced to run using the RUN procedure.

---

To change the interval of job execution from twice a day to once every two days:

SQL> begin

  2  dbms_job.interval(1,’sysdate + 2’);

  3 end;

  4 /



PL/SQL procedure successfully completed.

Forcing Jobs to Run

Sometimes when a job is already queued, you may want to execute it before the next execution time. You can force a job to run using the RUN procedure:

SQL> begin

  2  dbms_job.run(1);

  3 end;

  4 /



PL/SQL procedure successfully completed.

Job Queue Information in the Data Dictionary

As with anything else in Oracle, job queue information can be found in the data dictionary. Three views are used specifically to query job information:

Here’s a look at one of the views:

SQL> desc user_jobs

 Name                            Null?    Type

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

 JOB                             NOT NULL NUMBER

 LOG_USER                        NOT NULL VARCHAR2(30)

 PRIV_USER                       NOT NULL VARCHAR2(30)

 SCHEMA_USER                     NOT NULL VARCHAR2(30)

 LAST_DATE                                DATE

 LAST_SEC                                 VARCHAR2(8)

 THIS_DATE                                DATE

 THIS_SEC                                 VARCHAR2(8)

 NEXT_DATE                       NOT NULL DATE

 NEXT_SEC                                 VARCHAR2(8)

 TOTAL_TIME                               NUMBER

 BROKEN                                   VARCHAR2(1)

 INTERVAL                        NOT NULL VARCHAR2(200)

 FAILURES                                 NUMBER

 WHAT                                     VARCHAR2(4000)

 CURRENT_SESSION_LABEL                    MLSLABEL

 CLEARANCE_HI                             MLSLABEL

 CLEARANCE_LO                             MLSLABEL

 NLS_ENV                                  VARCHAR2(4000)

 MISC_ENV                                 RAW(32)





SQL> select job, broken, log_user, schema_user, last_date

  2 from user_jobs;



      JOB B LOG_USER            SCHEMA_USER          LAST_DATE

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

        1 Y RYAN                RYAN                 04-APR-98

Summary

A vast amount of territory has been covered in this chapter. There are so many responsibilities in the administration of an Oracle database that it would be difficult to list them all. However, all these responsibilities revolve around the common goals of protecting the data, providing desirable functionality, and optimizing performance as much as possible. These goals can be met through regular maintenance and scheduling of tasks.

Monitoring user and system processes is one of the first steps to a successful database management program. You should be aware of the various background processes that are running, as well as any other processes that run against the Oracle database. Initialization parameters are used to adjust the behavior of many of these background processes. Also understand that each type of process has its place in Oracle, whether it’s system, user, or batch. The goal of the DBA is to ensure that all processes can live happily together in the same instance.

Database maintenance involves the regular monitoring of the database. In order to perform good maintenance you must be proactive, studying the activities that occur in the database, such as space utilization, database growth, user functionality, and so on. Understanding the database is imperative to providing a better Oracle environment for the end user. Understanding is derived from study, and study is enabled through manual monitoring and database auditing.

Auditing provides the DBA with the opportunity to monitor specific activities in the database. Without the option of auditing, the DBA could be blind to what is happening in the database. Auditing yields knowledge if used properly. If you decide to activate auditing, be specific as to the tasks you decide to audit. An audit trail filled with a high percentage of unneeded information is a useless resource. You must be able to refer to the audit trail for resolutions, speculations, and even forecasting. Also keep in mind that auditing has its costs, so be frugal with your resources. Audit what you need, and most importantly, learn how to manage your audit trail.

We displayed a few common database monitoring scripts, but these are only a drop in the bucket. Much more can and should be implemented as part of the DBA’s everyday schedule. After monitoring scripts are in place, the scripts can be automated along with many other database-related tasks. Oracle provides the database user a means of scheduling jobs by placing them in a job queue. The scheduling of periodic tasks allows database users (especially the DBA) the opportunity to organize database activities effectively and often allows for the automation of many of our otherwise mundane tasks.