Re: Run command on sqlplus repetitively without reconnection.

As the owner of Perl:

perl -MCPAN -eshell

cpan> install DBI
cpan> install DBD::Oracle

Or get the sysadmin to do it for you.

If more involved than that, there is a lot of
information available by googling.

See "Perl for Oracle DBA's" for rather more
extensive instructions:

oreilly.com/catalog/oracleperl

Jared


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


  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 <http://www.ml.com/email_terms/>for
important additional terms relating to this e-mail.
http://www.ml.com/email_terms/
 ------------------------------





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

Other related posts: