Script: Monitor Oracle Database Connections

use strict;
use FileHandle;
use DBI;

# David Mann
# http://ba6.us

# read the compulsory arguments
die "\nOracle DB Connection Monitor\n\nsyntax: perl $0 user password instance\n" if @ARGV < 3;
my ( $user, $pass, $instance) = @ARGV;
my $filename=$ARGV[3];
my $output_to_file = 0;
my $OUT;

my $header = "TIMESTAMP,USER,MACHINE,SUM_PGAMEM,AVG_PGAMEM,COUNT\n";

# If output to file requested
if (@ARGV==4) {
print "Output to file " . $filename;
$output_to_file = 1;
open(OUT,">>$filename") or die "Cannot open $filename for writing.";
# $OUT->autoflush(0);
print OUT $header;
};

# Run a SELECT
my $dbh = DBI->connect( 'dbi:Oracle:' . $instance,
$user,
$pass,
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

my $sql = qq{
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS') || ',' ||
username || ',' ||
UPPER(machine) || ',' ||
TRIM(TO_CHAR(SUM(value), '999999999999')) || ',' ||
TRIM(TO_CHAR(AVG(value), '999999999999')) || ',' ||
COUNT(*) as OutputText,
COUNT(*) as Count
FROM v\$statname n,
v\$sesstat s,
v\$session ss
WHERE n.STATISTIC# = s.STATISTIC#
AND s.sid = ss.sid
AND n.name like 'session pga memory'
GROUP BY USERNAME, MACHINE
ORDER BY USERNAME, MACHINE
};

# Prepare and execute SELECT
my $sth = $dbh->prepare($sql);

do {

$sth->execute();

my($OutputText,$Count); # Declare columns
$sth->bind_columns(undef, \$OutputText, \$Count);

print "\n" . $header;
while( $sth->fetch() ) {
print "$OutputText\n";

# Beep if count > 20
if ($Count > 20) {
print chr(07);
};

# Output to file if required
if ($output_to_file = 1) {
print OUT "$OutputText\n";
};

}

sleep(20);

} while 1;

$sth->finish(); # Close cursor

$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstr\n";

close(OUT);