delete_partition.pl 刪除分割槽
#!/usr/bin/perl
$ENV{'ORACLE_HOME'}='/oracle/product/10.2.0';
$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.18/XXXdw.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
'192.168.2.13' => ['product/xxx@192.168.2.13/XXXdb',2,'BEIJING'],
# '192.168.2.14' => ['product/xxx@192.168.2.14/XXXdb',2,'BEIJING'],
'192.168.3.13' => ['product/xxx@192.168.3.13/XXXdb',2,'HANGZHOU'],
# '192.168.3.14' => ['product/xxx@192.168.3.14/XXXdb',2,'HANGZHOU'],
i'192.168.4.13' => ['product/xxx@192.168.4.13/XXXdb',2,'FUZHOU'],
# '192.168.4.14' => ['product/xxx@192.168.4.14/XXXdb',2,'FUZHOU'],
'192.168.6.13' => ['product/xxx@192.168.6.13/XXXdb',2,'GUANGXI'],
# '192.168.6.14' => ['product/xxx@192.168.6.14/XXXdb',2,'GUANGXI'],
'192.168.7.13' => ['product/xxx@192.168.7.13/XXXdb',2,'SICHUAN'],
# '192.168.7.14' => ['product/xxx@192.168.7.14/XXXdb',2,'SICHUAN'],
'192.168.8.13' => ['product/xxx@192.168.8.13/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
# '192.168.8.14' => ['product/xxx@192.168.8.14/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
'192.168.9.13' => ['product/xxx@192.168.9.13/XXXdb',2,'SHAN1XI'],
# '192.168.9.14' => ['product/xxx@192.168.9.14/XXXdb',2,'SHAN1XI'],
'192.168.10.13' => ['product/xxx@192.168.10.13/XXXdb',3,'LIAONING'],
# '192.168.10.14' => ['product/xxx@192.168.10.14/XXXdb',2,'LIAONING'],
'192.168.11.13' => ['product/xxx@192.168.11.13/XXXdb',5,'HENAN'],
# '192.168.11.14' => ['product/xxx@192.168.11.14/XXXdb',2,'HENAN'],
'192.168.12.13' => ['product/xxx@192.168.12.13/XXXdb',2,'SHAN3XI'],
# '192.168.12.14' => ['product/xxx@192.168.12.14/XXXdb',2,'SHAN3XI'],
# '192.168.13.13' => ['product/xxx@192.168.13.13/XXXdb',2,'JILIN'],
'192.168.13.14' => ['product/xxx@192.168.13.14/XXXdb',3,'JILIN'],
'192.168.14.13' => ['product/xxx@192.168.14.13/XXXdb',2,'SHANDONG'],
# '192.168.14.14' => ['product/xxx@192.168.14.14/XXXdb',2,'SHANDONG'],
'192.168.15.13' => ['product/xxx@192.168.15.13/XXXdb.jiangxi.XXX.com.cn ',4,'JIANGXI'],
# '192.168.15.14' => ['product/xxx@192.168.15.14/XXXdb.jiangxi.XXX.com.cn ',2,'JIANGXI'],
# '192.168.16.13' => ['product/xxx@192.168.16.13/XXXdb',2,'JIANGSU'],
'192.168.16.14' => ['product/xxx@192.168.16.14/XXXdb',2,'JIANGSU'],
'192.168.17.13' => ['product/xxx@192.168.17.13/XXXdb.shanghai.XXX.com.cn ',4,'SHANGHAI'],
# '192.168.17.14' => ['product/xxx@192.168.17.14/XXXdb.shanghai.XXX.com.cn ',3,'SHANGHAI'],
'192.168.18.13' => ['product/xxx@192.168.18.13/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],
# '192.168.18.14' => ['product/xxx@192.168.18.14/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],
'192.168.19.13' => ['product/xxx@192.168.19.13/XXXdb',2,'NEIMENGGU'],
# '192.168.19.14' => ['product/xxx@192.168.19.14/XXXdb',2,'NEIMENGGU'],
'192.168.20.13' => ['product/xxx@192.168.20.13/XXXdb',2,'HUBEI'],
# '192.168.20.14' => ['product/xxx@192.168.20.14/XXXdb',2,'HUBEI'],
'192.168.21.13' => ['product/xxx@192.168.21.13/XXXdb',2,'ANHUI'],
# '192.168.21.14' => ['product/xxx@192.168.21.14/XXXdb',3,'ANHUI'],
'192.168.22.13' => ['product/xxx@192.168.22.13/XXXdb',3,'XINJIANG'],
# '192.168.22.14' => ['product/xxx@192.168.22.14/XXXdb',3,'XINJIANG'],
'192.168.23.13' => ['product/xxx@192.168.23.13/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
# '192.168.23.14' => ['product/xxx@192.168.23.14/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
'192.168.24.ningxia' => ['cm_ningxia/xxx@192.168.24.13/XXXdb',3,'NINGXIA'],
# '192.168.24.ningxia' => ['cm_ningxia/xxx@192.168.24.14/XXXdb',3,'NINGXIA'],
'192.168.24.hainan' => ['cm_hainan/xxx@192.168.24.13/XXXdb',4,'HAINAN'],
# '192.168.24.hainan' => ['cm_hainan/xxx@192.168.24.14/XXXdb',4,'HAINAN'],
'192.168.24.xizang' => ['cm_xizang/xxx@192.168.24.13/XXXdb',3,'XIZANG'],
# '192.168.24.xizang' => ['cm_xizang/xxx@192.168.24.14/XXXdb',3,'XIZANG'],
'192.168.26.13' => ['product/xxx@192.168.26.13/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
# '192.168.26.14' => ['product/xxx@192.168.26.14/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
'192.168.27.13' => ['product/xxx@192.168.27.13/XXXdb',3,'GUIZHOU'],
# '192.168.27.14' => ['product/xxx@192.168.27.14/XXXdb',4,'GUIZHOU'],
'192.168.28.13' => ['product/xxx@192.168.28.13/XXXdb',14,'TIANJIN'],
# '192.168.28.14' => ['product/xxx@192.168.28.14/XXXdb',14,'TIANJIN'],
'192.168.29.13' => ['product/xxx@192.168.29.13/XXXdb',14,'HUNAN'],
# '192.168.29.14' => ['product/xxx@192.168.29.14/XXXdb',14,'HUNAN'],
}
);
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';
$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.18/XXXdw.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
'192.168.2.13' => ['product/xxx@192.168.2.13/XXXdb',2,'BEIJING'],
# '192.168.2.14' => ['product/xxx@192.168.2.14/XXXdb',2,'BEIJING'],
'192.168.3.13' => ['product/xxx@192.168.3.13/XXXdb',2,'HANGZHOU'],
# '192.168.3.14' => ['product/xxx@192.168.3.14/XXXdb',2,'HANGZHOU'],
i'192.168.4.13' => ['product/xxx@192.168.4.13/XXXdb',2,'FUZHOU'],
# '192.168.4.14' => ['product/xxx@192.168.4.14/XXXdb',2,'FUZHOU'],
'192.168.6.13' => ['product/xxx@192.168.6.13/XXXdb',2,'GUANGXI'],
# '192.168.6.14' => ['product/xxx@192.168.6.14/XXXdb',2,'GUANGXI'],
'192.168.7.13' => ['product/xxx@192.168.7.13/XXXdb',2,'SICHUAN'],
# '192.168.7.14' => ['product/xxx@192.168.7.14/XXXdb',2,'SICHUAN'],
'192.168.8.13' => ['product/xxx@192.168.8.13/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
# '192.168.8.14' => ['product/xxx@192.168.8.14/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
'192.168.9.13' => ['product/xxx@192.168.9.13/XXXdb',2,'SHAN1XI'],
# '192.168.9.14' => ['product/xxx@192.168.9.14/XXXdb',2,'SHAN1XI'],
'192.168.10.13' => ['product/xxx@192.168.10.13/XXXdb',3,'LIAONING'],
# '192.168.10.14' => ['product/xxx@192.168.10.14/XXXdb',2,'LIAONING'],
'192.168.11.13' => ['product/xxx@192.168.11.13/XXXdb',5,'HENAN'],
# '192.168.11.14' => ['product/xxx@192.168.11.14/XXXdb',2,'HENAN'],
'192.168.12.13' => ['product/xxx@192.168.12.13/XXXdb',2,'SHAN3XI'],
# '192.168.12.14' => ['product/xxx@192.168.12.14/XXXdb',2,'SHAN3XI'],
# '192.168.13.13' => ['product/xxx@192.168.13.13/XXXdb',2,'JILIN'],
'192.168.13.14' => ['product/xxx@192.168.13.14/XXXdb',3,'JILIN'],
'192.168.14.13' => ['product/xxx@192.168.14.13/XXXdb',2,'SHANDONG'],
# '192.168.14.14' => ['product/xxx@192.168.14.14/XXXdb',2,'SHANDONG'],
'192.168.15.13' => ['product/xxx@192.168.15.13/XXXdb.jiangxi.XXX.com.cn ',4,'JIANGXI'],
# '192.168.15.14' => ['product/xxx@192.168.15.14/XXXdb.jiangxi.XXX.com.cn ',2,'JIANGXI'],
# '192.168.16.13' => ['product/xxx@192.168.16.13/XXXdb',2,'JIANGSU'],
'192.168.16.14' => ['product/xxx@192.168.16.14/XXXdb',2,'JIANGSU'],
'192.168.17.13' => ['product/xxx@192.168.17.13/XXXdb.shanghai.XXX.com.cn ',4,'SHANGHAI'],
# '192.168.17.14' => ['product/xxx@192.168.17.14/XXXdb.shanghai.XXX.com.cn ',3,'SHANGHAI'],
'192.168.18.13' => ['product/xxx@192.168.18.13/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],
# '192.168.18.14' => ['product/xxx@192.168.18.14/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],
'192.168.19.13' => ['product/xxx@192.168.19.13/XXXdb',2,'NEIMENGGU'],
# '192.168.19.14' => ['product/xxx@192.168.19.14/XXXdb',2,'NEIMENGGU'],
'192.168.20.13' => ['product/xxx@192.168.20.13/XXXdb',2,'HUBEI'],
# '192.168.20.14' => ['product/xxx@192.168.20.14/XXXdb',2,'HUBEI'],
'192.168.21.13' => ['product/xxx@192.168.21.13/XXXdb',2,'ANHUI'],
# '192.168.21.14' => ['product/xxx@192.168.21.14/XXXdb',3,'ANHUI'],
'192.168.22.13' => ['product/xxx@192.168.22.13/XXXdb',3,'XINJIANG'],
# '192.168.22.14' => ['product/xxx@192.168.22.14/XXXdb',3,'XINJIANG'],
'192.168.23.13' => ['product/xxx@192.168.23.13/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
# '192.168.23.14' => ['product/xxx@192.168.23.14/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
'192.168.24.ningxia' => ['cm_ningxia/xxx@192.168.24.13/XXXdb',3,'NINGXIA'],
# '192.168.24.ningxia' => ['cm_ningxia/xxx@192.168.24.14/XXXdb',3,'NINGXIA'],
'192.168.24.hainan' => ['cm_hainan/xxx@192.168.24.13/XXXdb',4,'HAINAN'],
# '192.168.24.hainan' => ['cm_hainan/xxx@192.168.24.14/XXXdb',4,'HAINAN'],
'192.168.24.xizang' => ['cm_xizang/xxx@192.168.24.13/XXXdb',3,'XIZANG'],
# '192.168.24.xizang' => ['cm_xizang/xxx@192.168.24.14/XXXdb',3,'XIZANG'],
'192.168.26.13' => ['product/xxx@192.168.26.13/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
# '192.168.26.14' => ['product/xxx@192.168.26.14/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
'192.168.27.13' => ['product/xxx@192.168.27.13/XXXdb',3,'GUIZHOU'],
# '192.168.27.14' => ['product/xxx@192.168.27.14/XXXdb',4,'GUIZHOU'],
'192.168.28.13' => ['product/xxx@192.168.28.13/XXXdb',14,'TIANJIN'],
# '192.168.28.14' => ['product/xxx@192.168.28.14/XXXdb',14,'TIANJIN'],
'192.168.29.13' => ['product/xxx@192.168.29.13/XXXdb',14,'HUNAN'],
# '192.168.29.14' => ['product/xxx@192.168.29.14/XXXdb',14,'HUNAN'],
}
);
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");
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_id{$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;
`/home/oracle/admin/dailycheck/delete_partition_guangzhou.pl`;
{
printf LOGFILE "drop table $table partition:
\n";
foreach $ip (sort keys %{$station_id{$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;
`/home/oracle/admin/dailycheck/delete_partition_guangzhou.pl`;
@ARGV=("/home/oracle/admin/dailycheck/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/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:'¸÷¹¤×÷Õ¾PROCESSING_LOGSºÍ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 LOGFILE "$station_id\n";
my @partitionlist=`sqlplus -s /nolog <connect $connstr
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";
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++)
{
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";
{
my @l_record_cnt=`sqlplus -s /nolog <
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";}
}
return;
}
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27042095/viewspace-754618/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 刪除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)--增加和刪除索引分割槽索引
- ORACLE刪除-表分割槽和資料Oracle
- 刪除分割槽需要更新全域性索引索引
- windows10分割槽無法刪除怎麼辦_win10系統磁碟刪除分割槽的方法WindowsWin10
- SQL Server表分割槽刪除詳情DSCCSQLServer
- mysql 動態新建以及刪除分割槽表MySql
- 刪除分割槽更新全域性索引使用提醒索引
- ct_deletepartition.pl 刪除電信分割槽delete
- 資料表分割槽分割與刪除歷史資料
- 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硬碟
- 如何查詢分割槽表的分割槽及子分割槽
- 迴文分割；及刪除字元分析字元