ct_deletepartition.pl 刪除電信分割槽
#!/usr/bin/perl
$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='ct_warehouse/ct_warehouse@192.168.1.14/XXXdb.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
'192.168.64.jiangsu' => ['ct_jiangsu/xxx@192.168.64.13/XXXdb',3,'JIANGSU'],
'192.168.64.shandong' => ['ct_shandong/xxx@192.168.64.13/XXXdb',3,'SHANDONG'],
'192.168.64.hubei' => ['ct_hubei/xxx@192.168.64.13/XXXdb',3,'HUBEI'],
'192.168.64.shan3xi' => ['ct_shan3xi/xxx@192.168.64.13/XXXdb',3,'SHAN3XI'],
'192.168.64.shanghai' => ['ct_shanghai/xxx@192.168.64.13/XXXdb',3,'SHANGHAI'],
'192.168.52.beijing' => ['ct_beijing/xxx@192.168.52.13/XXXdb',3,'BEIJING'],
'192.168.52.TIANJIN' => ['ct_tianjin/xxx@192.168.52.13/XXXdb',3,'tianjin'],
'192.168.52.liaoning' => ['ct_liaoning/xxx@192.168.52.13/XXXdb',3,'LIAONING'],
'192.168.52.henan' => ['ct_henan/xxx@192.168.52.13/XXXdb',3,'HENAN'],
'192.168.52.hebei' => ['ct_hebei/xxx@192.168.52.13/XXXdb',3,'HEBEI'],
'192.168.52.sichuan' => ['ct_sichuan/xxx@192.168.52.13/XXXdb',3,'SICHUAN'],
'192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
'192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
'192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
'192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
'192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
'192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
'192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
'192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
'192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
'192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
# '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
# '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
# '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
# '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
# '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
# '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
# '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
# '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
}
);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
my $date_id=sprintf "%d%02d%02d",$year+1900,$mon+1,$mday;
$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='ct_warehouse/ct_warehouse@192.168.1.14/XXXdb.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
'192.168.64.jiangsu' => ['ct_jiangsu/xxx@192.168.64.13/XXXdb',3,'JIANGSU'],
'192.168.64.shandong' => ['ct_shandong/xxx@192.168.64.13/XXXdb',3,'SHANDONG'],
'192.168.64.hubei' => ['ct_hubei/xxx@192.168.64.13/XXXdb',3,'HUBEI'],
'192.168.64.shan3xi' => ['ct_shan3xi/xxx@192.168.64.13/XXXdb',3,'SHAN3XI'],
'192.168.64.shanghai' => ['ct_shanghai/xxx@192.168.64.13/XXXdb',3,'SHANGHAI'],
'192.168.52.beijing' => ['ct_beijing/xxx@192.168.52.13/XXXdb',3,'BEIJING'],
'192.168.52.TIANJIN' => ['ct_tianjin/xxx@192.168.52.13/XXXdb',3,'tianjin'],
'192.168.52.liaoning' => ['ct_liaoning/xxx@192.168.52.13/XXXdb',3,'LIAONING'],
'192.168.52.henan' => ['ct_henan/xxx@192.168.52.13/XXXdb',3,'HENAN'],
'192.168.52.hebei' => ['ct_hebei/xxx@192.168.52.13/XXXdb',3,'HEBEI'],
'192.168.52.sichuan' => ['ct_sichuan/xxx@192.168.52.13/XXXdb',3,'SICHUAN'],
'192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
'192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
'192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
'192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
'192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
'192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
'192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
'192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
'192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
'192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
# '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
# '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
# '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
# '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
# '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
# '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
# '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
# '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
}
);
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/ct_delete_partition$date_id.log");
printf LOGFILE "this script. start at %04d-%02d-%02d %02d:%02d:%02d\n\n",$year+1900,$mon+1,$mday,$hour,$min,$sec;
printf LOGFILE "this script. start at %04d-%02d-%02d %02d:%02d:%02d\n\n",$year+1900,$mon+1,$mday,$hour,$min,$sec;
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;
@ARGV=("/home/oracle/admin/dailycheck/ct_delete_partition$date_id.log");
$^I =".bak";
while (<>)
{
s/^([A-Z]*\n)/$1
\n/;
s/P\d+/$&/g;
s/\n/
\n/;
print ;
}
`cat /home/oracle/admin/dailycheck/ct_delete_partition$date_id.log| formail -I "From:oracle\@warehousep" -I "MIME-Version:1.0" -I "Content-type:text/html;charset=gb2312" -I "Content-Language:zh-cn" -I 'Subject:'µçП÷¹¤×÷Õ¾SMSG_LOGS±í·ÖÇøɾ³ýÈÕÖÖ¾| /usr/sbin/sendmail -oi oracle_ops\@7500.com.cn`;
#---------------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;
{
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;
@ARGV=("/home/oracle/admin/dailycheck/ct_delete_partition$date_id.log");
$^I =".bak";
while (<>)
{
s/^([A-Z]*\n)/$1
\n/;
s/P\d+/$&/g;
s/\n/
\n/;
print ;
}
`cat /home/oracle/admin/dailycheck/ct_delete_partition$date_id.log| formail -I "From:oracle\@warehousep" -I "MIME-Version:1.0" -I "Content-type:text/html;charset=gb2312" -I "Content-Language:zh-cn" -I 'Subject:'µçП÷¹¤×÷Õ¾SMSG_LOGS±í·ÖÇøɾ³ýÈÕÖÖ¾| /usr/sbin/sendmail -oi oracle_ops\@7500.com.cn`;
#---------------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 $ip,$connstr,$table,$days,$station_id,"\n\n";
#print $ip,$connstr,$table,$days,$station_id,"\n\n";
print LOGFILE "$station_id\n";
my @partitionlist=`sqlplus -s /nolog <
set line 200
set feedback off
set pagesize 10000
set head off
SELECT partition_name
FROM user_tab_partitions
WHERE UPPER (table_name) LIKE UPPER ('$table')
AND SYSDATE - TO_DATE (SUBSTR (partition_name, 2, 9), 'yyyymmdd') > $days
and length(partition_name)=9;
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++)
{
#ɾ³ýÏÂÃæÒ»ÐÐ
#print $partitionlist[$i],"\n";
{
#ɾ³ýÏÂÃæÒ»ÐÐ
#print $partitionlist[$i],"\n";
my @l_record_cnt=`sqlplus -s /nolog <connect $connstr
set line 200
set feedback off
set head off
select count(*) from $table partition($partitionlist[$i]);
exit
EOF
`;
chomp @l_record_cnt;
$l_record_cnt[1]=~s/\s//g;
my $table_arch=$table.'_arch';
my @g_record_cnt=`sqlplus -s /nolog <connect $warehouse
set line 200
set feedback off
set head off
SELECT COUNT (*)
FROM $table_arch subpartition ($partitionlist[$i]_$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";
set line 200
set feedback off
set head off
select count(*) from $table partition($partitionlist[$i]);
exit
EOF
`;
chomp @l_record_cnt;
$l_record_cnt[1]=~s/\s//g;
my $table_arch=$table.'_arch';
my @g_record_cnt=`sqlplus -s /nolog <
set line 200
set feedback off
set head off
SELECT COUNT (*)
FROM $table_arch subpartition ($partitionlist[$i]_$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";
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];
exit
EOF
`;
if($feedback=~m/Table altered/g)
{print LOGFILE "drop successful\n "; }
else
{print LOGFILE "$feedback
";}
{
my $feedback=`sqlplus -s /nolog <
alter table $table drop partition $partitionlist[$i];
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";}
else
{print LOGFILE "The record is not equal PLZ check*****\n";}
####################end
}
return;
}
return;
}
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27042095/viewspace-754620/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 刪除LINUX分割槽Linux
- Linux 分割槽刪除Linux
- swap分割槽新增刪除
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽
- 電腦硬碟分割槽要注意什麼,刪除硬碟分割槽的注意事項硬碟
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- ORACLE 刪除無用分割槽Oracle
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- 對刪除分割槽的分割槽表執行TSPITR
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- win10 oem分割槽怎麼刪除_win10 oem分割槽可以刪除嗎Win10
- win10分割槽好了怎麼刪除_win10分割槽完後如何刪除Win10
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- delete_partition.pl 刪除分割槽delete
- ORACLE刪除-表分割槽和資料Oracle
- 刪除分割槽需要更新全域性索引索引
- windows10分割槽無法刪除怎麼辦_win10系統磁碟刪除分割槽的方法WindowsWin10
- SQL Server表分割槽刪除詳情DSCCSQLServer
- mysql 動態新建以及刪除分割槽表MySql
- 刪除分割槽更新全域性索引使用提醒索引
- 資料表分割槽分割與刪除歷史資料
- win10刪除系統保留分割槽如何操作_win10刪除系統保留分割槽怎麼處理Win10
- 利用排程任務定時刪除分割槽
- 如何在 macOS Monterey(SSD 或 HDD)中刪除 Mac 分割槽Mac
- Oracle定時刪除、增加表分割槽(TOAD,PL/SQL)(轉)OracleSQL
- win10系統OEM分割槽怎麼合併或刪除Win10
- 在不刪除檔案的前提下合併硬碟分割槽硬碟
- 電腦分割槽桌布超清 電腦桌面桌布工作分割槽高清圖
- SQL SERVER資料庫檔案刪除、分割槽格式化解決方案SQLServer資料庫
- ASM磁碟分割槽或者裝置檔案刪除後恢復的方法ASM
- linux 未umount 目錄直接刪除分割槽後的處理方法Linux
- 系統優化之---分割槽表加速查詢速度和刪除速度優化
- 電腦分割槽桌布超清 電腦桌面桌布工作分割槽高畫質圖
- 分割槽表的分割槽資料刪除、truncate會對全域性和區域性索引產生什麼影響索引
- 刪除EFI系統分割槽(ESP)後Windows無法啟動,重建引導分割槽並修復啟動的過程Windows
- Diskpart 刪除OEM分割槽方法,set id=07 override 無效處理方法IDE
- Windows系統問題,兩個隱藏分割槽無法刪除問題.Windows
- Win7系統如何建立何建立、刪除或格式化硬碟分割槽Win7硬碟