一个shell 脚本用来同步表用的

最新推荐文章于 2021-10-19 15:23:18 发布

ckomuo086028

最新推荐文章于 2021-10-19 15:23:18 发布

阅读量127

点赞数

经常要将数据从外网同步到内网，一般会先drop掉内网的所有table后import数据进来。但是这样就会导致内网的AP不能使用。为将影响减少到最少，写了个脚本，将表一个个的导入进来，即truncate掉一个表再import一个表。[@more@]

1.需先将外网的数据都export出来，本例的dump文件是t_remhs.dmp,需同步的文件是t_remhs_5,t_remhs_4,t_remhs_3,t_remhs_2,t_remhs_1

[oracle@qht109 remhs_dump]$ exp l5m/l5m file=t_remhs.dmp tables=t_remhs_5,t_remhs_4,t_remhs_3,t_remhs_2,t_remhs_1

Export: Release 10.2.0.4.0 - Production on Wed Mar 11 15:48:17 2009

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T_REMHS_5 999 rows exported
. . exporting table T_REMHS_4 999 rows exported
. . exporting table T_REMHS_3 999 rows exported
. . exporting table T_REMHS_2 999 rows exported
. . exporting table T_REMHS_1 999 rows exported
Export terminated successfully without warnings.

2.注意：此方法只是同步目标库里有的表，目标库里没有的表需最后执行一次ignore=y的import操作。

3.建立的脚本如下：

[oracle@qht109 remhs_dump]$ cat batch_remhs.sh
#!/bin/sh
. /home/oracle/.bash_profile

export user=l5m
export passwd=l5m
export service=orcl109
export table_name=$1

sqlplus -silent ${user}/${passwd}@${service} << END

declare
table_name varchar2(100);
v_sql varchar2(200);
begin
table_name:='$1';
v_sql:='truncate table l5m.'||table_name;
execute immediate v_sql;
end;
/

exit;
END

imp l5m/l5m file=t_remhs.dmp log=t_remhs_$1.log tables=$1 ignore=y buffer=4096000

#end of file batch_remhs.sh

[oracle@qht109 remhs_dump]$ cat batch_retrive.sh
#!/bin/sh
. /home/oracle/.bash_profile

export user=l5m
export passwd=l5m
export service=orcl109

VALUE=`sqlplus -silent ${user}/${passwd}@${service} << END
set pagesize 0 feedback off verify off heading off echo off linesize 32000
select max(substr(sys_connect_by_path(table_name,','),2)) exp from
(select table_name,rownum rn from all_tables where owner='L5M' and table_name like 'T_REMHS%' )
start with rn=1 connect by rn=rownum;
exit;
END`

table_name=$VALUE

while [ 1 = 1 ]
do

cur_name=${table_name%%,*}
echo "cur_name is $cur_name"

table_name=${table_name#*,}
echo "table_name is $table_name"

sh batch_remhs.sh "$cur_name"

if [ $cur_name == $table_name ]; then
exit 0
fi

done

#end of file batch_retrive.sh

3.执行的结果如下：
[oracle@qht109 remhs_dump]$ ./batch_retrive.sh
cur_name is T_REMHS_5
table_name is T_REMHS_4,T_REMHS_3,T_REMHS_2,T_REMHS_1

PL/SQL procedure successfully completed.

Import: Release 10.2.0.4.0 - Production on Wed Mar 11 16:19:59 2009