海量資料遷移之外部表並行抽取
在10g開始的新特性中，外部表是一個不容忽視的好工具。對於大型專案中海量資料使用sqlloader是一種全新的方式，不過很明顯,sqlloader的可擴充套件性更強，但是基於oracle平臺的資料遷移來說，外部表的效能也不錯。對於資料遷移來說也是一個很好的方案。
使用外部表來做資料遷移，可以“動態”載入資料，能夠很方便的從資料庫中載入資料，對於資料校驗來說就顯得很有優勢了，而對於sqlloader來說，可能得等到資料載入的時候才知道是不是有問題，如果對於資料的準確性要求極高，可以使用外部表動態載入資料到備庫，和現有的資料做比對，減少在升級過程中帶來的災難。
還有關於資料型別，對於clob,blob的載入，大家都比較頭疼，在sqlloader中可能需要做一些額外的工作，來外部表中就和操作普通的表沒有什麼區別。
先來說說資料抽取的部分。
一下是我今天完成的部分指令碼，目錄結構如下。
drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:14 DUMP_LIST
drwxr-xr-x 2 ora11g dba 4096 Jun 9 23:25 extract
drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:32 parfile
[ora11g@rac1 ext_datapump]$ pwd
/u01/ora11g/test/ext_datapump
對於一些比較大的表，如果佔用的空間在好幾十個G左右的時候，生成一個巨大的dump檔案就有問題了，一來是關於io，順序的寫入dump檔案，而且在載入的時候也沒有其他的選擇了，只能從頭到尾一步一步來。
我的設想就是如果一個表有100G，可以把他切分為200個dump檔案，每個500M,或者說生成1000個dump檔案，每個dump檔案100M，這樣在載入的時候就可以很清楚的看到目前資料載入的進度了。
我使用瞭如下的指令碼來生成多個dump檔案，
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`
#for segment within 50M, will use parallel 1, and parallel will calculated with segment_bytes_size_MB/50M
page=50
sqlplus -s n1/n1 <
set head off
set line 100
set pages 0
set long 10000
set termout off
col segment_name for a40
col parallel format 9999
spool tab_parall_temp.lst
select distinct segment_name,ceil(sum(bytes/1024/1024)/50) parallel from user_segments
where segment_name in (select table_name from user_tables where table_name in ($tablst'x') and table_name not in (select table_name from user_external_tables))
group by segment_name order by 2 desc;
spool off;
EOF
sed '/^$/d' tab_parall_temp.lst |sort > ../parfile/tab_parall.lst
rm tab_parall_temp.lst
執行指令碼後，結果如下所示。
BIG_INSERT 1
CLOB_TEST 1
SMALL_INSERT 1
T 1
TEMP_TEST 1
TEST 1
TEST_DATA 1
TT 2
T_TEMP 1
可以看到表tt比較大，就需要分成了兩個dump。載入的時候也可以分批載入。
當然，生成的dump的個數也和一個資料庫引數密切相關，像我目前的庫，最大的並行只有30. 意味著如果我要把一個表切分成50個dump，根據配置，只能最多切分為30個。
parallel_max_servers integer 30
在生面的基礎上，可以採用下面的指令碼來生成dump檔案。
#### source owner $1
#### tab_name $2
#### target owner $3
#### dump directory $4
owner=`echo "$1"|tr '[a-z]' '[A-Z]'`
tab_name=`echo "$2"|tr '[a-z]' '[A-Z]'`
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`
tmp_parallel=`grep -w $tab_name ../parfile/tab_parall.lst|awk '{print $2}'`
for i in {1..$tmp_parallel};
do
echo \'${tab_name}_$i.dmp\', >> tmp_${tab_name}_par_dmp.lst
done
sed -e '/^$/d' -e '$s/.$//' tmp_${tab_name}_par_dmp.lst > ../DUMP_LIST/${tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/${tab_name}_par_dmp.lst`
#echo $dump_list
#echo $tmp_parallel
sqlplus -s n1/n1 <
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_tables where owner=upper(''$1'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then
dbms_output.put_line( 'TABLE '||'$tab_name'||' exists in owner account,proceed...');
else
dbms_output.put_line( 'TABLE does not exists in owner account,please check again');
return;
end if;
end;
/
set timing on
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_external_tables where owner=upper(''$3'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then
dbms_output.put_line( 'External table exists in owner account,proceed...');
execute immediate 'drop table $3.$2_ext';
end if;
end;
/
exec dbms_output.put_line('Get Dump file for $1.$2...');
create table $3.$2_ext
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY $4
LOCATION (
$dump_list
)
)
parallel $tmp_parallel
as
select /*+ parallel(t $tmp_parallel) */ * from $1.$2 t;
set feedback off;
set timing off
drop table $3.$2_ext;
EOF
rm tmp_${tab_name}_par_dmp.lst
exit
指令碼執行效果如下：
Get Dump file for n1.TT...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Table created.
Elapsed: 00:00:03.07
TABLE T_TEMP exists in owner account,proceed...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Get Dump file for n1.T_TEMP...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:01.39
生成的dump檔案如下：
[ora11g@rac1 expdp]$ ll *.dmp
-rw-r----- 1 ora11g dba 466944 Jun 10 01:43 BIG_INSERT_1.dmp
-rw-r----- 1 ora11g dba 12288 Jun 10 01:43 CLOB_TEST_1.dmp
-rw-r----- 1 ora11g dba 40960 Jun 10 01:43 SMALL_INSERT_1.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_1.dmp
-rw-r----- 1 ora11g dba 524288 Jun 10 01:43 TEMP_TEST_1.dmp
-rw-r----- 1 ora11g dba 466944 Jun 10 01:43 TEST_1.dmp
-rw-r----- 1 ora11g dba 69632 Jun 10 01:43 TEST_DATA_1.dmp
-rw-r----- 1 ora11g dba 39018496 Jun 10 01:43 TT_1.dmp
-rw-r----- 1 ora11g dba 43634688 Jun 10 01:43 TT_2.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_TEMP_1.dmp
在比較同樣的資料量的情況下，sqlloader可能需要大約120G的容量，而對於外部表來說，大概只需要40G左右的空間。
使用外部表來做資料遷移，可以“動態”載入資料，能夠很方便的從資料庫中載入資料，對於資料校驗來說就顯得很有優勢了，而對於sqlloader來說，可能得等到資料載入的時候才知道是不是有問題，如果對於資料的準確性要求極高，可以使用外部表動態載入資料到備庫，和現有的資料做比對，減少在升級過程中帶來的災難。
還有關於資料型別，對於clob,blob的載入，大家都比較頭疼，在sqlloader中可能需要做一些額外的工作，來外部表中就和操作普通的表沒有什麼區別。
先來說說資料抽取的部分。
一下是我今天完成的部分指令碼，目錄結構如下。
drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:14 DUMP_LIST
drwxr-xr-x 2 ora11g dba 4096 Jun 9 23:25 extract
drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:32 parfile
[ora11g@rac1 ext_datapump]$ pwd
/u01/ora11g/test/ext_datapump
對於一些比較大的表，如果佔用的空間在好幾十個G左右的時候，生成一個巨大的dump檔案就有問題了，一來是關於io，順序的寫入dump檔案，而且在載入的時候也沒有其他的選擇了，只能從頭到尾一步一步來。
我的設想就是如果一個表有100G，可以把他切分為200個dump檔案，每個500M,或者說生成1000個dump檔案，每個dump檔案100M，這樣在載入的時候就可以很清楚的看到目前資料載入的進度了。
我使用瞭如下的指令碼來生成多個dump檔案，
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`
#for segment within 50M, will use parallel 1, and parallel will calculated with segment_bytes_size_MB/50M
page=50
sqlplus -s n1/n1 <
set head off
set line 100
set pages 0
set long 10000
set termout off
col segment_name for a40
col parallel format 9999
spool tab_parall_temp.lst
select distinct segment_name,ceil(sum(bytes/1024/1024)/50) parallel from user_segments
where segment_name in (select table_name from user_tables where table_name in ($tablst'x') and table_name not in (select table_name from user_external_tables))
group by segment_name order by 2 desc;
spool off;
EOF
sed '/^$/d' tab_parall_temp.lst |sort > ../parfile/tab_parall.lst
rm tab_parall_temp.lst
執行指令碼後，結果如下所示。
BIG_INSERT 1
CLOB_TEST 1
SMALL_INSERT 1
T 1
TEMP_TEST 1
TEST 1
TEST_DATA 1
TT 2
T_TEMP 1
可以看到表tt比較大，就需要分成了兩個dump。載入的時候也可以分批載入。
當然，生成的dump的個數也和一個資料庫引數密切相關，像我目前的庫，最大的並行只有30. 意味著如果我要把一個表切分成50個dump，根據配置，只能最多切分為30個。
parallel_max_servers integer 30
在生面的基礎上，可以採用下面的指令碼來生成dump檔案。
#### source owner $1
#### tab_name $2
#### target owner $3
#### dump directory $4
owner=`echo "$1"|tr '[a-z]' '[A-Z]'`
tab_name=`echo "$2"|tr '[a-z]' '[A-Z]'`
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`
tmp_parallel=`grep -w $tab_name ../parfile/tab_parall.lst|awk '{print $2}'`
for i in {1..$tmp_parallel};
do
echo \'${tab_name}_$i.dmp\', >> tmp_${tab_name}_par_dmp.lst
done
sed -e '/^$/d' -e '$s/.$//' tmp_${tab_name}_par_dmp.lst > ../DUMP_LIST/${tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/${tab_name}_par_dmp.lst`
#echo $dump_list
#echo $tmp_parallel
sqlplus -s n1/n1 <
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_tables where owner=upper(''$1'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then
dbms_output.put_line( 'TABLE '||'$tab_name'||' exists in owner account,proceed...');
else
dbms_output.put_line( 'TABLE does not exists in owner account,please check again');
return;
end if;
end;
/
set timing on
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_external_tables where owner=upper(''$3'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then
dbms_output.put_line( 'External table exists in owner account,proceed...');
execute immediate 'drop table $3.$2_ext';
end if;
end;
/
exec dbms_output.put_line('Get Dump file for $1.$2...');
create table $3.$2_ext
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY $4
LOCATION (
$dump_list
)
)
parallel $tmp_parallel
as
select /*+ parallel(t $tmp_parallel) */ * from $1.$2 t;
set feedback off;
set timing off
drop table $3.$2_ext;
EOF
rm tmp_${tab_name}_par_dmp.lst
exit
指令碼執行效果如下：
Get Dump file for n1.TT...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Table created.
Elapsed: 00:00:03.07
TABLE T_TEMP exists in owner account,proceed...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Get Dump file for n1.T_TEMP...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:01.39
生成的dump檔案如下：
[ora11g@rac1 expdp]$ ll *.dmp
-rw-r----- 1 ora11g dba 466944 Jun 10 01:43 BIG_INSERT_1.dmp
-rw-r----- 1 ora11g dba 12288 Jun 10 01:43 CLOB_TEST_1.dmp
-rw-r----- 1 ora11g dba 40960 Jun 10 01:43 SMALL_INSERT_1.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_1.dmp
-rw-r----- 1 ora11g dba 524288 Jun 10 01:43 TEMP_TEST_1.dmp
-rw-r----- 1 ora11g dba 466944 Jun 10 01:43 TEST_1.dmp
-rw-r----- 1 ora11g dba 69632 Jun 10 01:43 TEST_DATA_1.dmp
-rw-r----- 1 ora11g dba 39018496 Jun 10 01:43 TT_1.dmp
-rw-r----- 1 ora11g dba 43634688 Jun 10 01:43 TT_2.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_TEMP_1.dmp
在比較同樣的資料量的情況下，sqlloader可能需要大約120G的容量，而對於外部表來說，大概只需要40G左右的空間。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1267553/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 海量資料遷移之分割槽並行抽取並行
- 海量資料遷移之資料抽取流程
- 海量資料遷移之外部表切分
- 海量資料處理_使用外部表進行資料遷移
- 海量資料遷移之外部表載入
- 海量資料遷移之使用分割槽並行切分匯入並行
- 海量資料遷移之分割槽並行切分並行
- 【實驗】【外部表】以資料泵檔案格式抽取and遷移資料演示
- 海量資料遷移之使用shell啟用多個動態並行並行
- 海量資料遷移之傳輸表空間(一)
- 海量資料遷移之透過rowid切分大表
- 海量資料遷移之通過rowid切分大表
- 海量資料遷移之資料載入流程
- 海量資料遷移之衝突資料篩查
- 外部表的另一種用途 資料遷移
- 海量資料遷移之透過shell估算資料量
- 海量資料遷移之通過shell估算資料量
- 從雲資料遷移服務看MySQL大表抽取模式MySql模式
- 海量資料遷移之sqlldr和datapump的缺點分析SQL
- 海量資料遷移之誤操作和防範建議
- 海量資料處理_資料泵分批資料遷移
- 海量資料遷移之分割槽表批次insert效能改進
- 海量資料遷移之分割槽表批量insert效能改進
- 使用impdp,expdp資料泵進入海量資料遷移
- 【移動資料】External Table 外部表
- 【資料遷移】使用傳輸表空間遷移資料
- 資料表內容遷移？
- 資料遷移（1）——通過資料泵表結構批量遷移
- 海量資料轉換遷移的程式碼自動生成
- GoldenGate新增加表進行資料抽取Go
- 資料庫物件遷移表空間資料庫物件
- 使用RMAN進行資料遷移
- 今天晚上進行資料遷移
- 資料庫遷移之資料泵實驗資料庫
- 海量資料遷移之一個誤操作的問題總結
- 遷移資料.
- Laravel 學習之資料庫遷移Laravel資料庫
- ORM實操之資料庫遷移ORM資料庫