#!/usr/bin/perlmy $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(*)");####定义报警的eventmy @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 写的巡检数据库的脚本所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）