VBTK::DBI::OraLogins - Monitoring of connections and blocking processes in Oracle

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

  VBTK::runAll;

This perl module is a front-end to the VBTK::DBI (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 monitor connections and blocked processes in an Oracle database.

The following methods are available to the common user.

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

This method calls 'new VBTK::DBI (VBTK::DBI)' after defaulting the parameters to best monitor connections and blocked processes 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 the defaults.

Interval

    Interval => 120,

Attr

    Attr => 'Oracle',

VBHeader

    VBHeader => [
      'Sid User      MemKB     Tot IO Delta IO Cmd Blkr Blkd Stat   Host        HostUser Prog                 PID       Ser#  ',
      '--- --------- ------ --------- -------- --- ---- ---- ------ ----------- -------- -------------------- --------- ------' ];

VBDetail

    VBDetail => [
      '@>> @<<<<<<<< @>>>>> @>>>>>>>> @>>>>>>> @>> @>>> @>>> @<<<<< @<<<<<<<<<< @<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<< @>>>>>',
      '@data[0..3],$delta[3],@data[4..12]' ];

SqlClause

    SqlClause => q(
        select sn.sid,
           sn.username,
           round(se.value/1024),
           io.block_gets,
           sn.command,
           blkr.blocks,
           blkd.blocks,
           sn.status,
           sn.machine,
           sn.osuser,
           sn.program,
           sn.process,
           sn.serial#
        from v$session sn,
             v$sesstat se,
             v$statname n,
             v$sess_io io,
             (select blocker.session_id sid,
              count(blocker.session_id) blocks
              from v$locked_object blocker, v$locked_object blocked
              where blocker.XIDUSN > 0
                and blocker.OBJECT_ID = blocked.OBJECT_ID
                and blocked.XIDUSN = 0
              group by blocker.session_id) blkr,
             (select blocked.session_id sid,
              count(blocked.session_id) blocks
              from v$locked_object blocker, v$locked_object blocked
              where blocker.XIDUSN > 0
                and blocker.OBJECT_ID = blocked.OBJECT_ID
                and blocked.XIDUSN = 0
              group by blocked.session_id) blkd
        where n.statistic# = se.statistic#
          and sn.sid = se.sid
          and n.name = 'session pga memory'
          and io.sid = sn.sid
          and sn.sid = blkr.sid(+)
          and sn.sid = blkd.sid(+)
        order by sn.sid );    

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

This method calls VBTK::DBI::addVBObj after defaulting unspecified parameters to best monitor connections and blocked processes 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.

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>.logins",

Rules

If any blocking in the database, then set the status to Warning.

    Rules => {
         '$data[5] > 0' => 'Warning' },

StatusHistoryLimit

Limit to storing the last 30 status changes

    StatusHistoryLimit => 30,

StatusUpgradeRules

Two 'Warning' statuses will upgrade to 'Failed'. Increase these values if the status is getting set to 'Failed' too often.

    StatusUpgradeRules =>
        'Upgrade to Failed if Warning occurs 3 times in (<Interval> * 4) seconds',

ExpireAfter

    ExpireAfter => (<Interval> * 3) seconds

Description

    Description = qq(
        This object monitors the users logged into <DSN>. );

VBTK

VBTK::DBI (VBTK::DBI)

VBTK::Server

VBTK::Parser

VBTK::ClientObject

Brent Henry, vbtoolkit@yahoo.com

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.