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 >$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);