[Chapter 13] 13.4 DBMS_JOB ExamplesChapter 13Job Scheduling in the Database 13.4 DBMS_JOB ExamplesThe DBMS_JOB package has all kinds of useful applications waiting to be discovered. DBAs can schedule jobs that look for problem conditions in the database or track and record resource utilization. Developers can schedule large batch operations at off hours without requiring operator intervention. 13.4.1 Tracking Space in TablespacesI decided to implement a very simple tracking system that can be used to track the growth of data in tablespaces. Such a system could be used for capacity planning or to trigger an alert of impending space problems.The system consists of a table called db_space, a view called tbs_space, and a procedure called space_logger. Here is the source code for the system:/* Filename on companion disk: job6.sql */* CREATE TABLE db_space (tablespace_name VARCHAR(30) NOT NULL ,calc_date DATE NOT NULL ,total_bytes NUMBER NOT NULL ,free_bytes NUMBER NOT NULL); CREATE OR REPLACE VIEW tbs_space (tablespace_name ,total_bytes ,free_bytes) AS SELECT DF.tbsname tablespace_name ,DF.totbytes total_bytes ,FS.freebytes free_bytes FROM (SELECT tablespace_name tbsname ,SUM(bytes) totbytes FROM dba_data_files GROUP BY tablespace_name ) DF ,(SELECT tablespace_name tbsname ,SUM(bytes) freebytes FROM dba_free_space GROUP BY tablespace_name ) FS WHERE DF.tbsname = FS.tbsname; CREATE OR REPLACE PROCEDURE space_logger AS /* || records total size and free space for all || tablespaces in table db_space || || Author: John Beresniewicz, Savant Corp || || 01/26/98: created || || Compilation requirements: || || SELECT on TBS_SPACE view || INSERT on DB_SPACE table */ CURSOR tbs_space_cur IS SELECT tablespace_name, total_bytes, free_bytes FROM tbs_space; BEGIN FOR tbs_space_rec IN tbs_space_cur LOOP INSERT INTO db_space VALUES (tbs_space_rec.tablespace_name ,SYSDATE ,tbs_space_rec.total_bytes ,tbs_space_rec.free_bytes); END LOOP; COMMIT; END space_logger;To set the system in motion, the space_logger procedure can be submitted to the job queue for regular execution as follows: DECLARE jobno NUMBER; BEGIN DBMS_JOB.SUBMIT (job => jobno ,what => 'begin space_logger; end;' ,next_date => SYSDATE ,interval => 'SYSDATE+1/24'); COMMIT; END; /Each time space_logger executes, it records total space, free space, tablespace name, and a timestamp for each tablespace in the database. Adjusting the interval parameter for the job adjusts the frequency of data collection. 13.4.2 Fixing Broken Jobs AutomaticallyCharles Dye recommended the next example, probably based on his experiences with replication. When jobs have relatively complex execution requirements in terms of the database objects on which they depend, they can easily become broken by incurring multiple execution failures. Perhaps the DBA has modified some database links or recreated tables or views, and the job's definition has been temporarily compromised. Well, it's a pain to manually reset the broken flag for these "not really broken" jobs, so why not have a job that regularly tries to unbreak jobs? Sounds good to me; here is a procedure called job_fixer to do just that:/* Filename on companion disk: job5.sql */* CREATE OR REPLACE PROCEDURE job_fixer AS /* || calls DBMS_JOB.BROKEN to try and set || any broken jobs to unbroken */ /* cursor selects user's broken jobs */ CURSOR broken_jobs_cur IS SELECT job FROM user_jobs WHERE broken = 'Y'; BEGIN FOR job_rec IN broken_jobs_cur LOOP DBMS_JOB.BROKEN(job_rec.job,FALSE); END LOOP; END job_fixer;The job_fixer procedure works only on a user's own jobs, so each user submitting jobs to the queue will need a separate job_fixer in the queue. 13.4.3 Self-Modifying and Self-Aware JobsThe ability to reference the job, next_date, and broken parameters in the job definition allows the procedure executed to alter its own job characteristics. Thus, a job could remove itself from the job queue, or assign its own next execution date based on some criteria decided at runtime by the procedure itself. I've written a small skeleton procedure that demonstrates this capability. It is called smart_job, and makes use of all three of the referenceable parameters when submitted as a job.When submitted to the job queue, smart_job uses the job definition parameters to modify itself in the following ways:Reschedules itself to parm1_IN minutes after finishing if parm2_IN = "RESTART"Sets next_date NULL causing automatic removal from queue if parm2_IN != "RESTART"Flags itself as broken if any exceptions are encounteredUses the job number to raise an exceptionPay close attention to how the smart_job procedure modifies itself. It uses the fact that the next_date and broken parameters support both IN and OUT modes when referenced by the job definition. Thus, when the broken_out parameter of smart_job has the broken parameter assigned to it in the call to DBMS_JOB.SUBMIT, the value set for broken_out by the procedure gets set for the job by the job queue when the job completes. In this way, smart_job changes its job characteristics without calling any DBMS_JOB procedures.Here is the source code for smart_job:/* Filename on companion disk: job4.sql */* PROCEDURE smart_job (parm1_IN IN INTEGER ,parm2_IN IN VARCHAR2 ,next_date_OUT IN OUT DATE ,broken_OUT IN OUT BOOLEAN ,job_IN IN INTEGER := -1) IS /* declare an exception for testing */ JOB_LESSTHAN_100 EXCEPTION; BEGIN /* || Do the procedure main line functions */ null; /* || use job_IN to branch to exception handler || for testing self-breaking */ IF job_IN < 100 THEN RAISE JOB_LESSTHAN_100; END IF; /* || After main processing is finished, job decides || if it should re-execute and determines its own || next execution date by adding parm1_IN minutes to || the current time */ IF parm2_IN = 'RESTART' THEN next_date_OUT := SYSDATE + parm1_IN/1440; ELSE /* || NULL next_date will cause automatic removal of || job from queue */ next_date_OUT := NULL; END IF; EXCEPTION /* || job "breaks" itself if unexpected error occurs */ WHEN OTHERS THEN broken_OUT := TRUE; END smart_job;The following test script exercises smart_job:/* Filename on companion disk: job4.sql */* var jobno NUMBER BEGIN /* || Test the ability to modify next_date. */ DBMS_JOB.SUBMIT (:jobno ,'smart_job(180,''RESTART'',next_date,broken,job);' ,SYSDATE + 1/1440 ,'SYSDATE + 1'); COMMIT WORK; END; / print jobno BEGIN /* || Test the ability to autoremove */ DBMS_JOB.SUBMIT (:jobno ,'smart_job(180,''NO_RESTART'',next_date,broken,job);' ,SYSDATE + 1/1440 ,'SYSDATE + 1'); COMMIT WORK; END; / print jobno BEGIN /* || Test the ability to break itself. */ DBMS_JOB.ISUBMIT (99 ,'smart_job(180,''RESTART'',next_date,broken,job);' ,SYSDATE + 1/1440 ,'SYSDATE + 1'); COMMIT WORK; END; /After executing the test script in SQL*Plus, the following jobs are in the queue:SQL> SELECT job,last_date,next_date,broken FROM user_jobs; JOB LAST_DATE NEXT_DATE B --------- ------------------- ------------------- - 307 1997:11:25:11:50:39 N 308 1997:11:25:11:50:39 N 99 1997:11:25:11:50:40 NA few minutes later, the job queue looks like this:SQL> SELECT job,last_date,next_date,broken FROM user_jobs; JOB LAST_DATE NEXT_DATE B --------- ------------------- ------------------- - 307 1997:11:25:11:50:42 1997:11:25:14:50:42 N 99 1997:11:25:11:50:42 1997:11:26:11:50:42 YThe tests worked! Job 308 ran once and was removed from the queue for having a NULL next_date. Job 307 ran and rescheduled itself three hours later, which is different from the interval specified in the call to DBMS_JOB.SUBMIT. Finally, job 99 set itself to broken status because its job number was less than 100.13.3 Tips on Using DBMS_JOBIV. Distributed Database Packages Copyright (c) 2000 O'Reilly & Associates. All rights reserved.