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_at_ml.com> 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_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 <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

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