NAME

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

SYNOPSIS

# 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;

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 monitor connections and blocked processes in an Oracle database.

PUBLIC METHODS

The following methods are available to the common user.

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

This method calls 'new 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>. );

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.