用perl指令碼檢測即將到期的date分割槽

該指令碼主要是利用我們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;