一、准备工作
1、源端
[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 29 15:07:35 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from T2_1 ;
COUNT(*)
----------
100000
2、目标端
--tns配置
test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 14:57:08 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create public database link dblink_test
2 connect to chf identified by xifenfei using 'test';
Database link created.
SQL> grant read,write on directory test_dir to xff;
Grant succeeded.
二、导入方式
1、expdp导出，impdp导入
[oracle@node1 ~]$ expdp chf/xff directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1
Export: Release 11.2.0.3.0 - Production on Tue Nov 29 15:05:36 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CHF"."T2_1" 9.326 MB 100000 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
/tmp/t1_2.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at 15:06:52
--1、userid使用的是目标端（expdp端）登录
--2、tables对应的表所有者需要和userid相同
[oracle@node1 tmp]$ ll /tmp/t1_2.dmp
-rw-r----- 1 oracle oinstall 9859072 11-29 15:06 /tmp/t1_2.dmp
[oracle@node1 tmp]$ impdp xff/xifenfei directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users
Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:30:15 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01": xff/******** directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T2_1" 9.326 MB 100000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at 15:30:18
[oracle@node1 tmp]$ sqlplus xff/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:30:43 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select count(*) from t2_1;
COUNT(*)
----------
100000
2、使用impdp直接导入
[oracle@node1 tmp]$ impdp chf/xff directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1
Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:48:49 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "CHF"."SYS_IMPORT_TABLE_01": chf/******** directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "XFF"."T2_1" 100000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at 15:49:00
--1、userid使用的是impdp端登录
--2、tables对应的表所有者需要和userid相同(tables的表所有者是源端，使用REMAP_SCHEMA映射owner)
[oracle@node1 tmp]$ sqlplus xff/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:51:18 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select count(*) from t2_1;
COUNT(*)
----------
100000
如果登录用户和需要导入的表用户名不同，暂不清楚怎么处理
