现在有数十万张表要从A库通过insert into tablename select * from tablename@dblink的方式导入到B库中。
B机上80个cpu,160G内存。希望能够大量并发执行。怎么写脚本呢?
谁有这方面的经验,麻烦指点一下。谢谢。
下面是我的脚本:
#!/usr/bin/sh
#最大进程数
v_max_session="310"
#进程计数器,用来计算当前正在执行的进程数量
v_session_counter=0
v_pipe_name=""
v_log_name=""
v_table_owner=""
v_table_name=""
v_part_name=""
v_param_file="list_table.txt"
f_insert_data()
{
sqlplus -s ${v_dest_tns} << EOF
set timing on time on autocommit on
prompt truncate table ...
declare
v_sql varchar2(300);
begin
begin
v_sql :='truncate table ${v_table_owner}.${v_table_name} reuse storage';
execute immediate v_sql;
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'truncate successfull',v_sql);
commit;
exception when others then
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'fail successfull',v_sql);
commit;
end;
begin
v_sql :='insert /*+ append */ into ${v_table_owner}.${v_table_name} nologging select * from ${v_table_owner}.${v_table_name}@JLDM';
execute immediate v_sql;
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'insert successfull',v_sql);
exception when others then
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'insert fail',v_sql);
commit;
end;
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'truncate successfull',v_sql);
exception when others then
insert into trunlog values ('${v_table_owner}','${v_table_name}','',sysdate,'fail','');
commit;
end;
/
exit;
EOF
}
while read v_table_owner v_table_name v_part_name
do
while [ $v_session_counter -ge $v_max_session ]
do
echo ". \c"
sleep 1
v_session_counter=0
ps -ef | grep -v grep | grep sqlplus | wc -l | read v_session_counter
done
f_insert_data
v_session_counter=0
ps -ef | grep -v grep | grep imp | wc -l | read v_session_counter
echo "session counter: ${v_session_counter}"
done
list_table.txt里面的内容如下:
JLCRM DW_NEWBUSI_SVC_MM_439 DW_NEWBUSI_SVC_MM200508
PARAM DIM_STAT_SP_TMP NULL
JLCRM DW_IMEI_USER_MM_431 DW_IMEI_USER_MM200509
DM DM_SMS_BASE_437_20090816 NULL
JLCRM DW_IMEI_USER_MM_432 DW_IMEI_USER_MM200509
DM DM_OWE_BASE_439_20090816 NULL
JLCRM DW_IMEI_USER_MM_433 DW_IMEI_USER_MM200509
DM DM_SMS_BASE_438_20090816 NULL
JLCRM DW_IMEI_USER_MM_434 DW_IMEI_USER_MM200509
第一列是用户名,第二列是表名,第三列是分区名。
我要实现的功能是,并发度300个。一起做insert into select 操作。
但我运行时发现,不能并发。每次只有一个insert操作。
麻烦有经验的朋友帮忙看看,改一下。谢谢。