NAME

VBTK::DBI::OraTableSpace - Monitoring of Oracle database tablespaces

SYNOPSIS

  # If you like all the defaults, then there's no need to over-ride them.
  $o = new VBTK::DBI::OraTableSpace (
      DSN     =>  'oracle.world',
      User    =>  'scott',
      Auth    =>  'tiger' 
  );
  $vbObj = $o->addVBObj ();

  VBTK::runAll;

DESCRIPTION

This perl module is a front-end to the VBTK::DBI class. It supports the same public methods as the VBTK::DBI class, but with common defaults to simplify the setup of a process to monitor tablespace usage in an Oracle database.

METHODS

The following methods are supported

$o = new VBTK::DBI::OraTableSpace (<parm1> => <val1>, <parm2> => <val2>, ...)

This method calls 'new VBTK::DBI' after defaulting the parameters to best monitor tablespace usage in an Oracle database. For a detailed description of the parameters, see VBTK::DBI. The defaults are as follows. If you like all the defaults then you don't have to pass in any parms, except for the DSN, User, and Auth parameters, which are required. Not all allowed parameters are listed here, just those which are defaulted.

Interval

    Interval => 120,

Attr

    Attr => 'Oracle',

VBHeader

    VBHeader => [
        'time               tablespace             tot_mb free_mb used  max_ext',
        '------------------ --------------------- ------- ------- ---- -------' ],

VBDetail

    VBDetail => [
        '@<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<< @>>>>>> @>>>>>> @>>% @>>>>>>',
        '$time,$data[0],int($data[1]),int($data[2]),int($data[3]),int($data[4])' ],

LogDetail

Same as VBDetail, but for the log file.

SqlClause

    SqlClause => q(
        select DT_TS_NAME,
               Sum_Alloc_Blocks * DB_BLOCK_FACTOR,
               Sum_Free_Blocks * DB_BLOCK_FACTOR,
               100-(100*Sum_Free_Blocks/Sum_Alloc_Blocks) AS PCT_USED,
               Max_Blocks * DB_BLOCK_FACTOR
        from 
         (select Tablespace_Name DT_TS_NAME,
                 SUM(Blocks) Sum_Alloc_Blocks
          from DBA_DATA_FILES
          group by Tablespace_Name),
         (select Tablespace_Name FS_TS_NAME,
                 MAX(Blocks)  AS Max_Blocks,
                 SUM(Blocks) AS Sum_Free_Blocks
          from DBA_FREE_SPACE
          group by Tablespace_Name),
         (select (value / 1024 / 1024) DB_BLOCK_FACTOR
          from v\$parameter
          where name = 'db_block_size')
        where DT_TS_NAME = FS_TS_NAME
        order by PCT_USED DESC ),

$vbObj = $o->addVBObj(<parm1> => <val1>, <parm2> => <val2>, ...)

This method calls VBTK::DBI::addVBObj after defaulting unspecified parameters to best monitor tablespace usage in an Oracle database. For a detailed description of the addVBObj parameters, see VBTK::Parser. The defaults are as follows. If you like all the defaults then you don't have to pass in any parms. Note that in this case, a separate VBObject is setup for each tablespace.

VBObjName

Construct a VBObjName using the Host, User, and DSN strings. Note that the User and DSN strings are checked for '.' characters and any found are converted to '-', so that we don't mess up the object's place in the hierarchy.

    VBObjName => ".<HOST>.db.<User>@<DSN>.dbspace.<tablespace-name>",

TextHistoryLimit

    TextHistoryLimit => 50,

item ReverseText

    ReverseText => 1,

Filter

Only show the rows for the current tablespace.

    Filter => '$data[0] eq <tablespace-name>',

Rules

If the tablespace contains the work 'TEMP', then the following defaults are applied. Temp tablespace should never get very full, so we set the thresholds lower.

    Rules => {
        '$data[3] > 30' => 'Warn',
        '$data[3] > 40' => 'Fail' },

Otherwise the following defaults are applied:

    Rules => {
        '$data[3] > 92' => 'Warn',
        '$data[3] > 94' => 'Fail' },

StatusHistoryLimit

Limit to storing the last 30 status changes

    StatusHistoryLimit => 30,

ExpireAfter

    ExpireAfter => '(<Interval> * 3) seconds',

Description

    Description = qq(
        This object monitors the tablespaces in <DSN>. );

RrdColumns

Store the pct utiliziation in the Rrd db, so that it can be graphed.

    RrdColumns => [ 'int($data[3])' ],

RrdCF

Use the 'MAX' consolidation function.

    RrdCF => 'MAX',

In addition to passing these defaults on in a call to VBTK::Wrapper::addVBObj, this method captures the resulting VBTK::ClientObject pointer ($vbObj) and makes the following calls to '$vbObj->addGraphGroup':

  $vbObj->addGraphGroup (
    GroupNumber    => 1,
    Labels         => 'pct-full',
    Title          => "Tablespace Utilization for <DSN>.<tablespace-name>",
    CF             => 'MAX',
  );

Finally, a second graph group is added which combines all the tablespace usage into a single graph.

  $vbObj->addGraphGroup (
    GroupNumber    => 2,
    DataSourceList => <list of tablespace-utilization object names>,
    Labels         => <list of tablespace names>,
    Title          => "Tablespace Utilization for <DSN>",
    CF             => 'MAX',
  );

This defines two graphGroups for the VBObject. See VBTK::ClientObject for details on the 'addGraphGroup' method.

SEE ALSO

VBTK

VBTK::DBI

VBTK::Server

VBTK::Parser

VBTK::ClientObject

AUTHOR

Brent Henry, vbtoolkit@yahoo.com

COPYRIGHT

Copyright (C) 1996-2002 Brent Henry

This program is free software; you can redistribute it and/or modify it under the terms of version 2 of the GNU General Public License as published by the Free Software Foundation available at: http://www.gnu.org/copyleft/gpl.html

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.