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

最新推荐文章于 2022-04-01 14:11:30 发布

congdiku5262

最新推荐文章于 2022-04-01 14:11:30 发布

阅读量145

点赞数

该脚本主要是利用我们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;