Re: Run command on sqlplus repetitively without reconnection.
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: Ajay_Thotangare@xxxxxx
- Date: Wed, 9 May 2007 10:06:45 -0700
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;
};
Other related posts:
