问题:
经常需要迁移大表数据,一般使用expdp/impdp、可移动表空间实现,也可以使用外部表。
1,源库,创建一个外部表
select count(*) from customer t;
22703336
set timing on
create table CUSTOMER_EXT
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dmp_dir
LOCATION ('cust1.dmp','cust2.dmp','cust3.dmp','cust4.dmp','cust5.dmp','cust6.dmp','cust7.dmp','cust8.dmp')
)
parallel 8
as
select /*+ parallel(c 8) */ * from customer c;
--Executed in 18.656 seconds
注:如果dmp文件清单只保留cust1.dmp,则无法并行,所需时间为。
2,在源数据库上调用
select dbms_metadata.get_ddl('TABLE','CUSTOMER_EXT') FROM DUAL;
3,目标库创建
CREATE TABLE CUSTOMER_EXT2
( "CUST_ID" NUMBER(*,0),
...
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DMP_DIR"
LOCATION
( 'cust1.dmp',
'cust2.dmp',
'cust3.dmp',
'cust4.dmp',
'cust5.dmp',
'cust6.dmp',
'cust7.dmp',
'cust8.dmp'
)
)
PARALLEL 8;
select count(*) from customer_ext2 t;
22703336
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-696747/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-696747/