Saturday, August 10, 2024
比如表T生成了两个dump文件（t_1.dmp,t_2.dmp)，就可以考虑如下的方式来加载，黄色部分是对应的dump文件。
CREATE TABLE T_EXT_1
( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob )
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "EXPDP_LOCATION"
LOCATION
( 't_1.dmp'
)
) ;
CREATE TABLE T_EXT_2
( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob )
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "EXPDP_LOCATION"
LOCATION
( 't_2.dmp'
)
) ;
对应的脚本如下：
其中在DUMP目录下存放着生成的dump文件，根据动态匹配得到最终生成了几个dump文件，来决定创建几个对应的外部表。
target_owner=`echo "2"|awk−F@′print$1′|awk−F/′print$1′|tr′[a−z]″[A−Z]′‘sourceowner=‘echo"1" |awk -F@ '{print 1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
tab_name=`echo "3"|tr '[a-z]' '[A-Z]'`
owner_account=5tmpparallel=‘ls−l../DUMP/{tab_name}_[0-9]*.dmp|wc -l`
echo parallel :tmpparallelforiin1..$tmpparallel;doecho\'{tab_name}_i.dmp\' >> tmp_{tab_name}_par_dmp.lst
done
sed -e '/^/d' tmp_{tab_name}_par_dmp.lst > ../DUMP_LIST/{tab_name}_par_dmp.lst
rm tmp_{tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/tabnamepardmp.lst‘print"conn1
set feedback off
set linesize 100
col data_type format a30
set pages 0
set termout off
SELECT
t1.COLUMN_NAME,
t1.DATA_TYPE
|| DECODE (
t1.DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || t1.DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || t1.DATA_LENGTH || ')') ||','
AS DATA_TYPE
from all_tab_columns t1 where owner=upper('owneraccount′)ANDtablename=upper(′3' )
order by t1.column_id;
"|sqlplus -s /nolog > {tab_name}.temp
sed -e '/^/d' -e 's/.//' -e 's/CLOB(4000)/CLOB/g' -e 's/BLOB(4000)/BLOB/g' tabname.temp>../DESCLIST/{tab_name}.desc
rm tabname.tempforiin1..$tmpparalleldoecholoadingtable{tab_name} as {tab_name}_EXT_i
sqlplus -s 2settimingonsetechoonCREATETABLE{tab_name}_EXT_i(‘cat../DESCLIST/{tab_name}.desc `
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY 4LOCATION(‘sed−n"{i}p" ../DUMP_LIST/${tab_name}_par_dmp.lst`
));
EOF
done
exit
生成的日志类似下面的格式：
loading table T as T_EXT_1
Elapsed: 00:00:01.33
loading table T as T_EXT_2
Elapsed: 00:00:01.30
