delete_partition_guangzhou.pl

#!/usr/bin/perl
use Time::Local;
use POSIX qw(strftime);

$ENV{'ORACLE_HOME'}='/oracle/product/10.2.0.1';
$ENV{'PATH'}="/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/bin:$ENV{'ORACLE_HOME'}/bin";
$ENV{'TERM'}='vt100';
our $warehouse='warehouse/warehouse@192.168.1.14/XXXdb.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
        '192.168.5.31' => ['product/xxx@192.168.5.31/XXXdb.guangzhou.XXX.com.cn',3,'GUANGZHOU'],
        }
);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
my $date_id=sprintf "%d%02d%02d",$year+1900,$mon+1,$mday;

open(LOGFILE, ">>/home/oracle/admin/dailycheck/delete_partition$date_id.log");

foreach $table (keys(%station))
{
  printf LOGFILE "drop  table $table partition:

---

\n";
    foreach $ip (sort keys %{$station{$table}} )
    {
        $connstr=$station{$table}{$ip}[0];
        $days=$station{$table}{$ip}[1];
        $station_id=$station{$table}{$ip}[2];
       delte_partition($ip,$connstr,$table,$days,$station_id);
    }
}
close LOGFILE;
#---------------delete__partition.sh-----------------------------------#
#--    delte station($ip)  partition  before $days days               -#
#--   the default partition peridor is week.if you want change the    -#
#--     default period plz change the SQL                             -#
#--  author: zhaoxin                                                  -#
#--                                                                   -#
#----------------------------------------------------------------------#
sub delte_partition {
if(@_!=5){die "THE PARAMETER NOT EQULE 5  ";}
my ($ip,$connstr,$table,$days,$station_id)=@_;
my $sql;

print LOGFILE "$station_id\n";
my @partitionlist=`sqlplus -s /nolog <connect $connstr
set line 200
set feedback off
set pagesize 10000
set head off
SELECT distinct SUBSTR (partition_name, 1, 9)
  FROM user_tab_partitions
 WHERE UPPER (table_name) LIKE UPPER ('$table')
   AND SYSDATE - TO_DATE (SUBSTR (partition_name, 2, 8), 'yyyymmdd') > $days;
exit
EOF
`;
chomp(@partitionlist);
if (@partitionlist<2)
{
 print LOGFILE "$ip: There is no partition need to drop before $days days now \n";

return;
};
print LOGFILE "can drop partition is :@partitionlist\n";

for ($i=1;$i<=$#partitionlist;$i++)
{

my @l_record_cnt=`sqlplus -s  /nolog <connect $connstr
set line 200
set feedback off
set head off
select count(*)  from  $table
where created_date>= TO_DATE (SUBSTR ('$partitionlist[$i]', 2, 9), 'yyyymmdd')
  and created_date < TO_DATE (SUBSTR ('$partitionlist[$i]', 2, 9), 'yyyymmdd')+1 ;
exit
EOF
`;
print @l_record_cnt;

chomp @l_record_cnt;
$l_record_cnt[1]=~s/\s//g;

my $table_arch=$table.'_arch';
$date= substr($partitionlist[$i],1,8);
$time= timelocal(0,0,0,substr($date,6,2),substr($date,4,2)-1,substr($date,0,4)-1900);
$time =$time+24*3600;
$warehouse_partition=  sprintf("%4d%02s%02s",(localtime ($time) )[5]+1900,(localtime ($time) )[4]+1,(localtime ($time) )[3] ,);

my @g_record_cnt=`sqlplus -s /nolog <connect $warehouse
set line 200
set feedback off
set head off
SELECT COUNT (*)
  FROM $table_arch  subpartition (p${warehouse_partition}_$station_id);
exit
EOF
`;

chomp @g_record_cnt;
$g_record_cnt[1]=~s/\s//g;
print LOGFILE "partition  $partitionlist[$i] record on station is:$l_record_cnt[1]\t";
print LOGFILE " on warehouse is:$g_record_cnt[1]\n";

#print "partition  $partitionlist[$i] record on station is:$l_record_cnt[1]\t";
#print  " on warehouse is:$g_record_cnt[1]\n";

if ($g_record_cnt[1]==$l_record_cnt[1] && $g_record_cnt[1]=~/^\d/)
{
my $feedback=`sqlplus -s /nolog <connect $connstr
alter table $table drop partition $partitionlist[$i]s1;
alter table $table drop partition $partitionlist[$i]s2;
alter table $table drop partition $partitionlist[$i]s3;
alter table $table drop partition $partitionlist[$i]s4;
exit
EOF
`;
        if($feedback=~m/Table altered/g)
                {print LOGFILE "drop successful\n "; }
        else
                {print LOGFILE "$feedback
";}

        }
else
        {print  LOGFILE "The record is not equal PLZ check*****\n";}

}
return;
}