Problem solve Get help with specific problems with your technologies, process and projects.

Generate a bar graph for tablespace utilization, part 2

A PERL script that will generate of bar graph of Oracle tablespace utilization.

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.

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;

    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,\$ts_avail tsa,\$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];

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

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.