RE: Run command on sqlplus repetitively without reconnection.

I tried to execute perl script. I get following error

Can't locate DBI.pm in @INC (@INC contains:
/usr/perl5/5.6.1/lib/sun4-solaris-64int /usr/perl5/5.6.1/lib
/usr/perl5/site_perl/5.6.1/sun4-solaris-64int /usr/perl5/site_perl/5.6.1
/usr/perl5/site_perl /usr/perl5/vendor_perl/5.6.1/sun4-solaris-64int
/usr/perl5/vendor_perl/5.6.1 /usr/perl5/vendor_perl .) at event.pl line
3.

BEGIN failed--compilation aborted at event.pl line 3.

 [oraunidw] mhs-clust89-qc:/unidwh-ds001/ora01/app/oracle/ajayt/test:

 

I don't know perl. I need to figure out what is this DBI. If you have
some simple doc on this please let me know

 

  _____  

From: Jared Still [mailto:jkstill@xxxxxxxxx] 
Sent: Wednesday, May 09, 2007 1:07 PM
To: Thotangare, Ajay (GTI)
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Run command on sqlplus repetitively without reconnection.

 

On 5/9/07, Thotangare, Ajay (GTI) <Ajay_Thotangare@xxxxxx> wrote:

        select event,count(*) from v$session group by event;

First you must decide if you really meant v$session_event 
or v$session_wait.

Then you need to use perl, as seen at end of email.

Sample output:

===========================================
   SQL*Net message from client          3
                        imm op          1 
                    pmon timer          1
             rdbms ipc message          7
                     sbtbackup          1
                    smon timer          1
           wakeup time manager          1 
===========================================
   SQL*Net message from client          3
                        imm op          1
                    pmon timer          1
             rdbms ipc message          7 
                     sbtbackup          1
                    smon timer          1
           wakeup time manager          1
===========================================
   SQL*Net message from client          3 
                        imm op          1
                    pmon timer          1
             rdbms ipc message          7
                     sbtbackup          1
                    smon timer          1 
           wakeup time manager          1

Cleaning up...

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


use warnings;
use FileHandle;
use DBI;
use strict;

use Getopt::Long;

my %optctl = ();

Getopt::Long::GetOptions(
   \%optctl,
   "database=s",
   "username=s", 
   "password=s",
   "sysdba!",
   "sysoper!",
   "z","h","help");

my($db, $username, $password, $connectionMode);

$connectionMode = 0; 
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }

if ( ! defined($optctl{database}) ) {
   usage(1);
}
$db=$optctl{database};

if ( ! defined($optctl{username}) ) { 
   usage(2);
}

$username=$optctl{username};
$password = $optctl{password};

#print "USERNAME: $username\n";
#print "DATABASE: $db\n";
#print "PASSWORD: $password\n"; 
#exit;

my $dbh = DBI->connect(
   'dbi:Oracle:' . $db,
   $username, $password,
   {
      RaiseError => 1,
      AutoCommit => 0,
      ora_session_mode => $connectionMode 
   }
   );

die "Connect to  $db failed \n" unless $dbh;

$dbh->{ora_check_sql} = 0;
$dbh->{RowCacheSize} = 100;

my $sql=q{select event,count(*) event_count from v$session_wait group by
event}; 

my $sth = $dbh->prepare($sql);

$SIG{QUIT}=\&cleanup;
$SIG{INT}=\&cleanup;
$SIG{KILL}=\&cleanup;
$SIG{TERM}=\&cleanup;
$SIG{ABRT}=\&cleanup;

while (1) {

   $sth->execute; 

   print "===========================================\n";
   while( my $ary = $sth->fetchrow_arrayref ) {
      my ($event,$eCount) = @{$ary};
      printf "%30s %10d\n",$event,$eCount; 
      #print "$event - $eCount\n";
   }

   sleep 2;
}


$sth->finish;
$dbh->disconnect;

sub cleanup {
   printf "\nCleaning up...\n";
   $sth->finish; 
   $dbh->disconnect;
   exit;
}
sub usage {
   my $exitVal = shift;
   $exitVal = 0 unless defined $exitVal;
   use File::Basename;
   my $basename = basename($0);
   print qq/

usage: $basename 

  -database      target instance
  -username      target instance account name
  -password      target instance account password
  -sysdba        logon as sysdba
  -sysoper       logon as sysoper

  example:

  $basename -database dv07 -username scott -password tiger -sysdba
/;
   exit $exitVal;
};
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the sender, 
delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------

Other related posts: