Generate a bar graph for tablespace utilization, part 2

Ed. note: This tip is a follow-up to a recently published tip Generate a bar graph for tablespace utilization.

This is a good tip indeed but it takes two or three steps before one can see the graph. Here is a Perl version of it that does the same with one click. It works very well except for the password part; I am using OPS$ id in the real script.

#!/usr/local/perl5.00506/bin/perl
use DBI;
use CGI qw(:standard);
use GD::Graph::bars;
use Carp;

my $Header = Header;
my $Footer = Footer;
my (@Arr1,@Arr2,@Arr3);

my $Ora_Sid = param('Ora_Sid');
my $Ora_User = param('Ora_User');
my $Ora_Passwd = param('Ora_Passwd');
my $i=0;


my $dbh = DBI->connect("dbi:Oracle:$Ora_Sid", $Ora_User, $Ora_Passwd) or
          die "Can't Connect to Oracle: $DBI::errstr";
$| = 1;

    my $Sql_Stmt0 = "select count(*) from dba_tablespaces";
    my $sth1 = $dbh->prepare($Sql_Stmt0);
    $sth1->execute() or die "Can't Execute statement: $DBI::errstr";
    my $count = $sth1->fetchrow_array;
    $sth1->finish;


    my $Sql_Stmt1 = "select t.tablespace_name ,
           decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) ,
           (tsa.bytes / 1024) ,
           ((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024),
           (decode(tsf.bytes, null, 0, tsf.bytes) / 1024),
           (round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100))
        from sys.dba_tablespaces t, sys.sm\$ts_avail tsa, sys.sm\$ts_free tsf
        where t.tablespace_name = tsa.tablespace_name
        and   t.tablespace_name = tsf.tablespace_name (+)
        order by 6 desc, t.status, t.tablespace_name";

    my $sth1 = $dbh->prepare($Sql_Stmt1)
                or die "Can't prepair statement: $DBI::errstr";
    if ( $sth1) {
        $sth1->execute() or die "Can't Execute statement: $DBI::errstr";
        while (my @Tmp = $sth1->fetchrow_array)
        {
            $Arr1[$i] = sprintf("%s(%s)",$Tmp[0],$Tmp[1]);
            $Arr2[$i++] = $Tmp[5];
        }
    }
    $sth1->finish;

$dbh->disconnect or die "Can't Disconnect from Oracle!";

$Image = GD::Graph::bars->new($count*20, 500);
$Image->set ( x_label       => 'Tablespace Names',
             y_label       => 'Percent Used',
             bar_spacing   => '2',
             x_labels_vertical => 1,
             show_values => 1,
             dclrs => [ qw(blue red) ],
             title         => 'Graph of Login distribution');
@Arr3=(\@Arr1,\@Arr2);
$Image->set_legend("eccdb69", "fcdb69");
my $format = $Image->export_format;
print header("image/$format");
binmode STDOUT;
print $Image->plot(\@Arr3)->$format(); 

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.