用shell批量转储表

最近经常做一些转储表的工作，之前的脚步太简单了，人工干预工作太多(手工重构清理脚步，整合转储日志等工作)，于是对之前的脚步进行更新！

之前的脚本如下：

cat /oracle/lu/expcxdb_table.out|while read username tablename
do
exp \'sys/oracle  as sysdba\' tables="$username"."$tablename" buffer=498430400  direct=y recordlength=65535 file=/orabak/"$tablename" indexes=y constraints=y compress=n log=/oracle/lu/cxdblog/exp_"$tablename".log
done

修改后的脚本如下

#清理垃圾文件
rm clear_data.txt
rm cd_dump_cxdb_table_all.log

#构建清理脚本，获取数据总行数
while read username tablename
do
echo "alter table $username"."$tablename" "  rename to  od_""$tablename" >>clear_data.txt
i=$(($i+1))
done </oracle/lu/expcxdb_table.out
echo "总数据条目为:"$i"条">>cd_dump_cxdb_table_all.log
cat /oracle/lu/expcxdb_table.out|while read username tablename
do
echo "drop table "  "$username"."od_""$tablename">>clear_data.txt
done

#导出要转储的表
j=0
cat /oracle/lu/expcxdb_table.out|while read username tablename
do
j=$(($j+1))
k=$(($i-$j))
exp \'sys/oracle  as sysdba\' tables="$username"."$tablename" buffer=498430400  direct=y recordlength=65535 file=/orabak/"$tablename" indexes=y constraints=y compress=n log=/oracle/lu/cxdblog/exp_"$tablename".log
dmpsize=`du -sm /orabak/$tablename.dmp|awk -F' ' '{print $1}'`
cat /oracle/lu/cxdblog/exp_"$tablename".log|grep "without warnings"
if [[ $? -eq 0 ]];then
echo "第" "$j" "张表" ":$username"."$tablename" " 已导出,dump文件大小为:" "$dmpsize" "M 剩余" "$k" "张表" "无告警提示 详细日志如下：">>cd_dump_cxdb_table_all.log
echo "                                                   ">>cd_dump_cxdb_table_all.log

#统计所有转储日志到同一个日志文件
cat /oracle/lu/cxdblog/exp_"$tablename".log>>cd_dump_cxdb_table_all.log
echo "                                                   ">>cd_dump_cxdb_table_all.log
echo "----------------------表间分割线-------------------">>cd_dump_cxdb_table_all.log
echo "                                                   ">>cd_dump_cxdb_table_all.log
else
echo "第" "$j" "张表" ":$username"."$tablename" " 已导出,dump文件大小为:" "$dmpsize" "M 剩余" "$k" "张表" "有告警提示 详细日志如下：">>cd_dump_cxdb_table_all.log
cat /oracle/lu/cxdblog/exp_"$tablename".log>>cd_dump_cxdb_table_all.log
echo "                                                   ">>cd_dump_cxdb_table_all.log
echo "----------------------表间分割线-------------------">>cd_dump_cxdb_table_all.log
echo "                                                   ">>cd_dump_cxdb_table_all.log
fi
done

脚本首先会自动构建清理表的脚本

[oracle@localhost lu]$ cat clear_data.txt 
alter table hzq.table1   rename to  od_table1
alter table hzq.table2   rename to  od_table2
alter table hzq.table3   rename to  od_table3
alter table hzq.table4   rename to  od_table4
alter table hzq.table5   rename to  od_table5
alter table hzq.table6   rename to  od_table6
alter table hzq.table7   rename to  od_table7
alter table hzq.table8   rename to  od_table8
alter table hzq.table9   rename to  od_table9
alter table hzq.table10   rename to  od_table10
alter table hzq.table11   rename to  od_table11
drop table  hzq.od_table1
drop table  hzq.od_table2
drop table  hzq.od_table3
drop table  hzq.od_table4
drop table  hzq.od_table5
drop table  hzq.od_table6
drop table  hzq.od_table7
drop table  hzq.od_table8
drop table  hzq.od_table9
drop table  hzq.od_table10
drop table  hzq.od_table11

能够统计总共要清理表的数量，后台执行脚本，通过日志能够实时查看脚本转储到了哪张表，查看转储出的日志大小，具体如下

[oracle@localhost lu]$ cat cd_dump_cxdb_table_all.log |more
总数据条目为:11条
第 1 张表 :hzq.table1  已导出,dump文件大小为: 1 M 剩余 10 张表 无告警提示 详细日志如下：
                                                   

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
Current user changed to HZQ
. . exporting table                         TABLE1         12 rows exported
Export terminated successfully without warnings.
                                                   
----------------------表间分割线-------------------
                                                   
第 2 张表 :hzq.table2  已导出,dump文件大小为: 1 M 剩余 9 张表 无告警提示 详细日志如下：
                                                   

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
Current user changed to HZQ
. . exporting table                         TABLE2          1 rows exported
Export terminated successfully without warnings.
                                                   
----------------------表间分割线-------------------
                                                   
第 3 张表 :hzq.table3  已导出,dump文件大小为: 1 M 剩余 8 张表 无告警提示 详细日志如下：
                                                   

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
Current user changed to HZQ
. . exporting table                         TABLE3          1 rows exported
Export terminated successfully without warnings.