p e r l 与 数 据 库 基 本 信 息

 

sub query{
 my ($sid)= @_;
 print "/nSID: $sid/n";

 open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
   print INDEX_HTML ("<br><br><B><font size=/"4/"> SID: $sid </font></B><br>/n");
   close INDEX_HTML;

 open (LOG,">>dbsystem.log")||die("Could not open log .");
  print LOG ("/n[SID: $sid]/n");
  close LOG;

 my $sql="select a.a1 cur,b.b1 max,cast(a.a1/b.b1/*100 as Decimal(5,2))||'%' as rate "
 ."from (select count(/*) as a1 from v/$session where user/#!=0) a ,"
 ."(select v2.value b1 from v/$parameter v2 where v2.name=/'processes/') b";
 my $resf="/tCUR/tMAX/tRATE/n";
 &querysql ($sid,"sessions",$sql,$resf);

 $sql="SELECT UPPER(F.TABLESPACE_NAME) /"ts_name/",cast(D.TOT_GROOTTE_MB/1024 as decimal(10,2)) as /"ts_size/",cast(F.TOTAL_BYTES/1024 as decimal(10,2)) as /"free_size/","
 ."cast((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024 as Decimal(10,2)) as /"Used/","
 ."TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB /* 100,2/),/'990.99/'/)||'%' /"rate/" "
 ."FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 /* 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 /* 1024), 2) MAX_BYTES "
 ."FROM SYS.DBA_FREE_SPACE where tablespace_name not in (/'UNDOTBS/', /'UNDOTBS1/', /'PERFSTAT/', /'INDX/', /'XDB/', /'ODM/',"      
 ."/'TOOLS/', /'DRSYS/', /'CWMLITE/', /'EXAMPLE/') GROUP BY TABLESPACE_NAME) F, "                   
 ."(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 /* 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD " 
 ."GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME  ORDER BY 4 DESC";
 $resf="/tTS_NAME/tTS_SIZE/tFREE/tUSED/tUSE_RATE/n";              

 &querysql ($sid,"tablespace (GB)",$sql,$resf);
         

 

}

sub querysql{
 my ($sid)=$_[0];
 my ($obj)=$_[1];
 my ($sql)=$_[2];
 my ($resf)=$_[3];
 print "obj: ".$obj."/n";

 open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
  print INDEX_HTML ("<p><font size=/"2/" face=/"Verdana/" color=/"#000093/"><br>obj: $obj <br><br>/n");
  close INDEX_HTML;
 

 my $dbh = DBI ->connect("dbi:Oracle:$sid","$user","$pass",{'RaiseError' => 1});

 $sth=$dbh->prepare("$sql");

 $sth->execute;

 print $resf;

 open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
   print INDEX_HTML ("$resf <br>/n");
   close INDEX_HTML;

 

 while (@res=$sth->fetchrow_array) {

  foreach (@res){
   print ("/t".$_);
   open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
    print INDEX_HTML ("$_&nbsp&nbsp");
    close INDEX_HTML;

 

   }
   print "/n";
   open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
    print INDEX_HTML ("<BR>");
    close INDEX_HTML;

   
   
   if ($obj eq "sessions")
     { ($rate_r,)=split("%",$res[2]);
    if ($rate_r > $session_alarm_rate){
     print "session over criterion!";
     open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
     print INDEX_HTML ("<font color=red>session over criterion($session_alarm_rate%)!</font><br>");
     close INDEX_HTML;
     my $freerate=int((100-$rate_r)*100)/100;
     &sm ("$sid session only have $freerate% avi!");
    }
     }

    if ($obj eq "tablespace (GB)")
     { ($rate_r,)=split("%",$res[4]);
    if ($rate_r > $ts_alarm_rate){
     print "tablespace over criterion!/n";
     open (INDEX_HTML,">>$index_path")||die("Could not open index.html .");
     print INDEX_HTML ("<font color=red>tablespace over criterion($ts_alarm_rate%)!</font><br>");
     close INDEX_HTML;
     $freerate=int((100-$rate_r)*100)/100;

     if (!($res[0] eq "SYSAUX"))
     {&sm ("$sidtable space $res[0]only have $freerate% avi!");
     }
    }
     }
    
 }