The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

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.