經常要將資料從外網同步到內網，一般會先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 } << 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 } << 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