ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用perl脚本检测即将到期的date分区

用perl脚本检测即将到期的date分区

原创 Linux操作系统 作者:myownstars 时间:2011-07-01 13:24:35 0 删除 编辑

该脚本主要是利用我们partition的创建格式Pyymmdd(按天分区)或者Pyymm(按月分区),将其中的yy和mm分别提取出来,然后与当前的日期比较,相差等于一个月的时候会发出邮件报警;

脚本代码如下:
首先同过pl/sql调用utl_file将想要输出的数据输出到文本文件;
然后利用perl判断该文件是否为空,如不为空则发送该文件到指定的邮箱进行通知
-bash-3.2$ more justin.pl
#!/usr/bin/perl -W
use Tie::File;
use warnings;

$ENV{ORACLE_HOME}="/data/oracle/product/10205/db1";
$ENV{ORACLE_SID} ='justin';

my $file ='/data/oracle/pump/alert_partition.txt';

my $sql = qq{
declare
  v_max varchar2(20);
  v_max_year number := 0;
  v_max_month number := 0;
  v_cur_year number;
  v_cur_month number;
  v_cur_day number;
  l_output utl_file.file_type;
begin
  --open the file and put it empty
  l_output := utl_file.fopen('PUMP','alert_partition.txt','W'); 
  for i in (select distinct table_name from dba_tab_partitions where table_owner='JUSTIN') loop
    --get the max partition of the table
    select max(substr(partition_name,2,4)) into v_max  from dba_tab_partitions where table_name = i.table_name and table_owner='JUSTIN';
    select substr(to_number(v_max),1,2),substr(to_number(v_max),3,2) into v_max_year,v_max_month from dual;
    --get the sysdate value
    select to_number(to_char((trunc(sysdate)),'yy')),to_number(to_char((trunc(sysdate)),'mm')) into v_cur_year,v_cur_month from dual;
    if v_cur_year = v_max_year and v_cur_month = v_max_month -1 then--when the table's max partition is 1108 and current is 1107
      dbms_output.put_line(i.table_name ||'''s partition is going to be exceeded;');
    elsif v_cur_year = v_max_year - 1 and v_cur_month = v_max_month + 11 then--when its max partition is 1201 and current is 1112
      utl_file.put_line(l_output,i.table_name ||'''s partition is going to be exceeded;');
    end if;
  end loop;
  utl_file.fclose(l_output);
end;
/
};

my $result =`/data/oracle/product/10205/db1/bin/sqlplus / as sysdba <             $sql 
             EOF`;

tie my @array, 'Tie::File', $file or die "$!";

if ( @array ) {

  $ip=`/sbin/ifconfig eth0|grep "inet addr"|awk "{print $2}"`;
  $ip = substr($ip,10,20);
  `sendEmail -s mail.******.com -f justin\@******.com -t justin\@******.com -u 'Warning!! the partition table is going to be expired! in $ip' -o message-file=/data/oracle/pump/alert_partition.txt`;
  print "Email sent\n";
}

创建测试表
SQL> create table justin(id number(10),riqi date)
  2  partition by range (riqi)
  3  ( partition P1107 values less than (to_date('2011-07-01','yyyy-mm-dd')) );
 
Table created
 
SQL> alter table justin add partition p1108 values less than (to_date('2011-08-01','yyyy-mm-dd'));
 
Table altered
调试运行
-bash-3.2$ perl justin.pl
Email sent
同时收到邮件
justin's partition is going to be exceeded;


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-701122/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112687