Perl 写的巡检数据库的脚本

Perl 前端之家
前端之家收集整理的这篇文章主要介绍了Perl 写的巡检数据库的脚本，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
#!/usr/bin/perl
my $red="\e[1;31m";
my $green="\e[1;32m";
my $yellow="\e[1;33m";
my $normal="\e[0m";
use DBI; 
my  $dbName = 'june';  
my  $dbUser = 'test';  
my  $dbUserPass = 'test';  
my  $dbh = DBI->connect("dbi:Oracle:$dbName",$dbUser,$dbUserPass) or die "can't connect to database " ;
$dbh->{LongReadLen} = 100;
$dbh->{LongTruncOk} = 1;

sub section() {
    my $section=shift;
    print ">>>>>$green $section $normal  \n";
};
sub get_lock {
§ion("LOCK INFO");
my $hostsql = qq{select  t3.sid,t4.object_name,t3.MACHINE,t3.PROGRAM,t2.ctime \
   from v\$process t1,\ 
        (select sid,type,id1,id2,lmode,request,ctime \
           from v\$lock \
          where  sid in (select sid \
                          from v\$lock \
                         where TYPE = 'TX' \
                           and LMODE = 6) \
                           and type='TM') t2,\
        v\$session t3,dba_objects t4 \
  where t2.sid = t3.sid \ 
    and t1.addr = t3.paddr \
    and t2.id1=t4.object_id };
my ($a,$b,$c,$e,$f,$g);
my $selStmt = $dbh->prepare($hostsql);  
$selStmt->bind_columns(undef,\$a,\$b,\$c,\$d,\$e);  
$selStmt->execute();  
           printf("%-10s%-20s%-40s%-25s%-10s\n",SID,OBJECT_NAME,MACHINE,PROGRAM,CTIME);
while( $selStmt->fetch() ){ 
        if ($e > 10){
          printf("%-10s%-20s%-40s%-25s${red}%-10s${normal}%s\n","$a",$d,$e);

}};  
$selStmt->finish;  
#$dbh->disconnect or warn "DB disconnect Failed: $DBI::errstr\n";  
};
#####################################################################
sub get_top_seg{
my ($a,$g);
§ion("TOP SEG");
 my $hostsql=qq{select * \ 
  from (select segment_name,sum(bytes / 1024 / 1024)\ 
          from dba_segments\ 
         group by segment_name\ 
         order by 2 desc)\ 
 where rownum < 10}; 
my $selStmt = $dbh->prepare($hostsql);   
$selStmt->bind_columns(undef,\$b);
$selStmt->execute();   
       printf("%-50s%-6s\n","SEGMENT_NAME","BYTES(MB)");  
while( $selStmt->fetch() ){  
        printf ("%-50s%-6s\n",$a,$b);
}   
$selStmt->finish;  

};
############################################################################
sub temp_usg{
§ion("Temp USAGE");
my ($a,$g);
 my $hostsql=qq{select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,round((1-free_blocks/total_blocks) * 100)  from v\$sort_segment};  
my $selStmt = $dbh->prepare($hostsql);    
$selStmt->bind_columns(undef,\$e,\$f); 
$selStmt->execute();    
      printf ("%-20s%-20s%-20s%-20s%-20s%-20s\n","TABLESPACE","CURRENT_USERS","TOTAL_BLCOKS","USED_BLOCKS","FREE_BLOCKS","USED_PCT");
while( $selStmt->fetch() ){   
    if ("$f" > 70 ){
     printf ("%-20s%-20s%-20s%-20s%-20s${red}%-20s${normal}%s\n","$b","$c","$d","$e","$f")
;
}   else{printf ("%-20s%-20s%-20s%-20s%-20s%-20s%s\n","$f")}
 };   
$selStmt->finish;   
 
};
###############################################################################
sub sort_ses{ 
§ion("SORT SESSION"); 
my ($a,$c); 
 my $hostsql=qq{select sid,username,program from v\$session where saddr in (select session_addr from V\$TEMPSEG_USAGE)};   
my $selStmt = $dbh->prepare($hostsql);     
$selStmt->bind_columns(undef,\$c);  
$selStmt->execute();     
      printf ("%-20s%-20s%-20s\n","SID","USERNAME","PROGRAM"); 
while( $selStmt->fetch() ){    
   {printf ("%-20s%-20s%-20s\n","$c")} 
 };    
$selStmt->finish;    
  
};
################################################################################
sub sort_info{
§ion("SORT INFORMATION");  
my ($a,$d);  
 my $hostsql=qq{select username,SESSION_ADDR,sql_id,segtype from V\$TEMPSEG_USAGE };    
my $selStmt = $dbh->prepare($hostsql);      
$selStmt->bind_columns(undef,\$d);   
$selStmt->execute();      
      printf ("%-20s%-20s%-20s%-20s\n","SESSION_ADDR","sql_ID","SEGTYPE");  
while( $selStmt->fetch() ){     
   {printf ("%-20s%-20s%-20s%-20s\n","$d")}  
 };     
$selStmt->finish;     
   
}; 
#################################################################################
sub db_info{ 
§ion("DATABASE INFORMATION");   
my ($a,$g);   
 my $hostsql=qq{select b.host_name,name,platform_name,b.version,created,log_mode,open_mode from v\$database a,v\$instance b};     
my $selStmt = $dbh->prepare($hostsql);       
$selStmt->bind_columns(undef,\$f,\$g);    
$selStmt->execute();       
      printf ("%-20s%-20s%-30s%-20s%-20s%-20s%-20s\n","HOST_NAME","DB_NAME","PLATFORM_NAME","VERSION","DB CREATED","LOG_MODE","OPEN_MODE");   
while( $selStmt->fetch() ){      
   {printf ("%-20s%-20s%-30s%-20s%-20s%-20s%-20s\n","$f","$g")}   
 };      
$selStmt->finish;      
    
};
#######################################################################################
sub undo_usage{  
§ion("UNDO INFORMATION");    
my ($a,$b);    
 my $hostsql=qq{select tablespace_name,retention from dba_tablespaces where tablespace_name=(select VALUE from v\$parameter where name ='undo_tablespace')};      
my $selStmt = $dbh->prepare($hostsql);        
$selStmt->bind_columns(undef,\$b);     
$selStmt->execute();        
      printf ("%-20s%-20s\n","TABLESPACE_NAME","RETENTION");    
while( $selStmt->fetch() ){       
   {printf ("%-20s%-20s\n","$b")}    
 };       

$selStmt->finish;       
my ($c,$d);
my $hostsql=qq{select * from (select sum(bytes)/1024/1024 use_mb,status from dba_undo_extents group by status)};
my $selStmt = $dbh->prepare($hostsql);         
$selStmt->bind_columns(undef,\$d);      
$selStmt->execute();         
      printf ("%-20s%-20s\n","USED_MB","STATUS");     
while( $selStmt->fetch() ){        
   {printf ("%-20s%-20s\n","$d")}     
 };  

if ("$b" eq "NOGUARANTEE"){
my ($e); 
my $hostsql=qq{select nvl((select USE_MB from (select sum(bytes)/1024/1024 use_mb,status from dba_undo_extents group by status) where STATUS='ACTIVE'),0) from dual};
my $selStmt = $dbh->prepare($hostsql);  

$selStmt->bind_columns(undef,\$e);       

$selStmt->execute();
   printf ("%-20s\n","UNDO USAGE(MB)");
while( $selStmt->fetch() ){         
   {printf ("%-20s\n","$e")}      
 };
}else {

my ($f);  
my $hostsql=qq{select nvl((select used_mb \
          from (select sum(bytes) / 1024 / 1024 as used_mb,status \
                  from dba_undo_extents \
                 group by status)\ 
         where STATUS = 'ACTIVE'),0) + \
       nvl((select used_mb \
          from (select sum(bytes) / 1024 / 1024 as used_mb,status\
                  from dba_undo_extents \ 
                 group by status)\ 
         where STATUS = 'UNEXPIRED'),0)\
  from dual  };
my $selStmt = $dbh->prepare($hostsql);   
$selStmt->bind_columns(undef,\$e);        
$selStmt->execute(); 
   printf ("%-20s\n","UNDO USAGE(MB)"); 
while( $selStmt->fetch() ){          
   {printf ("%-20s\n","$e")}       
 }; 
}
}; 
###############################################################################################
sub get_undo_seg{ 
my ($a,$d); 
§ion("UNDO SEGMENT USAGE"); 
 my $hostsql=qq{select s.sid,s.username,u.name,t.USED_UBLK \
  from v\$transaction t,v\$rollstat r,v\$rollname u,v\$session s \
 where s.taddr = t.addr \
   and t.xidusn = r.usn \
   and r.usn = u.usn \
 order by s.username};
my $selStmt = $dbh->prepare($hostsql);    
$selStmt->bind_columns(undef,\$d); 
$selStmt->execute();    
       printf("%-15s%-20s%-40s%s\n","NAME","USED_UBLK");   
while( $selStmt->fetch() ){   
       printf("%-15s%-20s%-40s%s\n","$d");   
}    
$selStmt->finish;   
 
};
######################################################################################################
sub get_tran{  
§ion("TRANSACTION  COUNT");    
my $hostsql=qq{select count(*) from v\$transaction};      
my $sth = $dbh->prepare($hostsql);
$sth->execute();
my $count = $sth->fetchrow_array();
print "\TRANSACTION  COUNT is $count\n";
$sth->finish;
     
}; 
#####################################################################################################
sub get_session{
§ion("SESSION  COUNT");
my $hostsql=qq{select count(*) from v\$session};
my $sth = $dbh->prepare($hostsql);
$sth->execute();
my $count = $sth->fetchrow_array();
print "\SESSION  COUNT is $count\n";
$sth->finish;

};
#######################################################################################################
sub get_tbs{   
§ion("TABLESPACE  USAGE");     
my ($a,$e);  
 my $hostsql=qq{select a.tablespace_name,round(a.total_size,1) "total(M)",\   
  round(a.total_size)-round(nvl(b.free_size,0),1) "used(M)",\   
  round(nvl(b.free_size,1) "free(M)",\   
 round(100- round(nvl(b.free_size,0)/total_size*100,1)) "used rate(%)" \  
  from (select tablespace_name,sum(bytes)/1024/1024 total_size \   
  from dba_data_files \   
  group by tablespace_name) a,\   
  (select tablespace_name,sum(bytes)/1024/1024 free_size \   
  from dba_free_space \   
group by tablespace_name) b \   
  where a.tablespace_name = b.tablespace_name(+) \   
order by "used rate(%)" };
my $selStmt = $dbh->prepare($hostsql);     
$selStmt->bind_columns(undef,\$e);  
$selStmt->execute();     
       printf("%-25s%-20s%-20s%-20s%-20s\n","TOTAL(MB)","USED(MB)","FREE(MB)","USED RATE(%)");    
while( $selStmt->fetch() ){    
      if ("$e" > 80){
       printf("%-25s%-20s%-20s%-20s${red}%-20s${normal}%s\n","$e");    
     }else
    {
       printf("%-25s%-20s%-20s%-20s%-20s%s\n","$e");    
     }
}     
$selStmt->finish;    
  
}; 
################################################################################################
sub getTime{

    my $time = shift || time();
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time);

    $year += 1900;
    $mon ++;
    $min  = '0'.$min  if length($min)  < 2;
    $sec  = '0'.$sec  if length($sec)  < 2;
    $mon  = '0'.$mon  if length($mon)  < 2;
    $mday = '0'.$mday if length($mday) < 2;
    $hour = '0'.$hour if length($hour) < 2;
    my $weekday = ('Sun','Mon','Tue','Wed','Thu','Fri','Sat')[$wday];

    return { 'second' => $sec,'minute' => $min,'hour'   => $hour,'day'    => $mday,'month'  => $mon,'year'   => $year,'weekNo' => $wday,'wday'   => $weekday,'yday'   => $yday,'date'   => "$year-$mon-$mday"
          };

}

my $date = &getTime();
my $now_datetime =$date->{'year'}.'-'.$date->{'month'}.'-'.$date->{'day'}." ".$date->{'hour'}.":00:00";
#print "\$now_datatime is $now_datetime\n";
my $old_time =$date->{'year'}.'-'.$date->{'month'}.'-'.$date->{'day'}." ".($date->{'hour'} -1).":00:00";
#print "\$old_time is $old_time\n";
#######################################################################################################
sub get_top_iosql{ 
my ($a,$g,$h,$i); 
§ion("TOP 10 DISK READ sql"); 
 my $hostsql=qq{select * \
  from (select a.parsing_schema_name,\
               sum(a.executions_delta) executions,\
               sum(a.DISK_READS_delta) disk_reads,\
               sum(a.DIRECT_WRITES_delta) direct_writes,\
               round(sum(a.cpu_TIME_delta) / 1000000 / 60,2) cpu_time_min,\
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60,2) elapsed_time_min,\
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,2) physical_read_gb,\
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,2) physical_write_gb,\
               substr((select sql_text\
                  from v\$sqlarea c\
                 where c.sql_id = a.sql_id\
                   and rownum = 1),1,100)\
          from DBA_HIST_sqlSTAT a,DBA_HIST_SNAPSHOT b\
         where a.SNAP_ID = b.SNAP_ID\
           and b.BEGIN_INTERVAL_TIME >=\
               to_date('$old_time','YYYY-MM-DD HH24:MI:SS') ---开始时间  \  
           and END_INTERVAL_TIME <=\
               to_date('$now_datetime','YYYY-MM-DD HH24:MI:SS') ---结束时间  \
         group by parsing_schema_name,a.sql_id\
         order by 7 desc)\
 where rownum <= 10};
my $selStmt = $dbh->prepare($hostsql);    
print "\$hostsql is $hostsql\n";
$selStmt->bind_columns(undef,\$g,\$h,\$i); 
$selStmt->execute();    
       printf("%-15s%-20s%-40s%s\n","USED_UBLK");   
while( $selStmt->fetch() ){   
       printf("%-15s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%s\n","$g","$h","$i");   
}    
$selStmt->finish;   
 
};
#############################################################################################################
sub get_event{
§ion("WAIT   EVENT");
my ($a,$b);
my $hostsql=qq{select event,count(*) from v\$session where event not like ('%idle%')  group by event};
my $selStmt = $dbh->prepare($hostsql);
$selStmt->bind_columns(undef,\$b);
$selStmt->execute();
       printf("%-100s%-20s%s\n","EVENT","COUNT(*)");
####定义报警的event
my @warn_event=('cursor: pin S wait on X','latch: cache buffers chains','buffer busy waits ','db file scattered read','library cache pin','read by other session','direct path read','log file sync','library cache lock','latch: shared pool','row cache lock','enq: TX - row lock contention','log buffer space','library cache: mutex X','log file parallel write');
while( $selStmt->fetch() ){
      if (grep m/$a/,@warn_event ){
       printf("${red}%-100s${normal}${red}%-20s${normal}%s\n","$b");
     }else
    {
       printf("%-100s%-20s%s\n","$b");
     }
};
$selStmt->finish;

};
      
&db_info();
&get_lock();
&get_top_seg();
&temp_usg();
&sort_ses();
&sort_info();
&undo_usage();
&get_undo_seg();
&get_tran();
&get_session();
&get_tbs();
&get_event();

猜你在找的Perl相关文章

漫谈 Perl 的 web 应用开发框架
忍不住在 PerlChina 邮件列表中盘点了一下 Perl 里的 Web 应用框架（巧的是 PerlBuzz 最近...
perl中bless的理解
bless有两个参数：对象的引用、类的名称。 类的名称是一个字符串，代表了类的类型信息，这...
PERL GB2312 UTF-8 编码转换
gb2312转Utf的方法： use Encode; my $str = "中文"; $str_cnsoftware = encode(&...
perl 计算硬盘利用率
  perl 计算硬盘利用率， 以%来查看硬盘资源是否存在IO消耗cpu资源情况； 部份代码参考了...
perl之变量
1 简单变量 Perl 的 Hello World 是怎么写的呢？请看下面的程序： #!/usr/bin/perl print ...
Perl 中的正则表达式
正则表达式是 Perl 语言的一大特色，也是 Perl 程序中的一点难点，不过如果大家能够很好的...
Perl和Shell区别
在学习Perl和Shell时，有很多人可能会问这样一个问题，到底先学习哪个或者学习哪个更好！ ...
Perl命令行应用介绍
Perl 有很多命令行参数. 通过它, 我们有机会写出更简单的程序. 在这篇文章里我们来了解...
perl的特殊变量
转自： http://bbs.chinaunix.net/thread-1191868-1-1.html# 让你的perl代码看起来更像per...
Perl BackConnectShell + Rootlab t00l
2009-02-02 13:07 #!/usr/bin/perl # D.O.M TEAM - 2007 # anonyph; arp; ka0x; xarnuz # ...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积