13.4.1 Tracking Space in Tablespaces

I 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 Automatically

Charles 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 Jobs

The 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 encountered

• Uses the job number to raise an exception

Pay 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 N

A 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 Y

The 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.