RE: Run command on sqlplus repetitively without reconnection.

From: Thotangare, Ajay \(GTI\) <Ajay_Thotangare_at_ml.com>
Date: Wed, 9 May 2007 13:41:14 -0400
Message-ID: <C8C1CE9973039245BF52C0FC3DF02F60C5C846@MLNYA222MB.amrs.win.ml.com>

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_at_gmail.com] Sent: Wednesday, May 09, 2007 1:07 PM
To: Thotangare, Ajay (GTI)
Cc: oracle-l_at_freelists.org
Subject: Re: Run command on sqlplus repetitively without reconnection.  

On 5/9/07, Thotangare, Ajay (GTI) <Ajay_Thotangare_at_ml.com> 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/
--------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 09 2007 - 12:41:14 CDT