Oracle Built-in Packages

DBMS_JOB Examples

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

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