Re: Run command on sqlplus repetitively without reconnection.

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 9 May 2007 10:06:45 -0700
Message-ID: <bf46380705091006w7728443bs7e252bdd4568a31a@mail.gmail.com>

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

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

Received on Wed May 09 2007 - 12:06:45 CDT