ORACLE expdp匯出遠端庫指定使用者資料到本地資料庫
眾所周知，ORACLE匯出遠端庫時，我們可以使用exp，但是exp的效率不高，相比之下expdp可以開並行，速度更可控，
下面就說下使用dblink結合expdp匯出遠端庫指定使用者到本地資料庫的方法。
首先，在本地庫建立發起expdp及dblink的使用者，並賦予合適的許可權
[oracle@linux ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 09:29:38 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user zhul identified by zhul;
User created.
SQL> grant dba to zhul;
Grant succeeded.
其次，本地建立dblink
SQL> create public database link orcl10g31
connect to zhul identified by zhul
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl10g)
)
) ';
Database link created.
再次，本地建立expdp客戶端使用的directory路徑
SQL> create directory zhul as '/home/oracle/';
Directory created.
測試dblink對遠端庫的訪問可用性
SQL> select count(*) from scott.emp@orcl10g31;
COUNT(*)
----------
14
SQL>
使用expdp及建立好的dblink匯出遠端庫的指定使用者
[oracle@linux ~]$ expdp zhul/zhul directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31
Export: Release 11.2.0.4.0 - Production on Tue Jul 19 09:35:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ZHUL"."SYS_EXPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows
. . exported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows
. . exported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows
. . exported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows
. . exported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows
Master table "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZHUL.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/shijiazhuang20160819.dmp
Job "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 19 09:35:55 2016 elapsed 0 00:00:30
在本地建立好接收遠端庫指定使用者資料的本地使用者，然後使用impdp匯入遠端庫指定使用者的資料到本地指定使用者下
SQL> create user shijiazhuang identified by shijiazhuang default tablespace shijiazhuang;
User created.
SQL> grant resource to shijiazhuang;
Grant succeeded.
SQL> grant create session to shijiazhuang;
Grant succeeded.
[oracle@linux ~]$ impdp zhul/zhul directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang
Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:36:50 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ZHUL"."SYS_IMPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHIJIAZHUANG" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows
. . imported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows
. . imported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows
. . imported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows
. . imported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ZHUL"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Jul 19 09:37:06 2016 elapsed 0 00:00:15
使用過程中可能遇到的問題：
建立dblink時儘量不要使用tnsname解析，要使用tnsname裡的服務全稱，否則匯出時會遇到如下報錯：
C:\Users\localadmin>expdp zhul/zhul directory=myexpdp dumpfile=shijiazhuang20160
819.dmp logfile=shijiazhuang20160719.log schemas=shijiazhuang network_link=orcl1
0g31
Export: Release 11.2.0.1.0 - Production on 星期二 7月 19 09:15:04 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: 引數值無效
ORA-39200: 連結名稱 "orcl10g31" 無效。
ORA-02019: 未找到遠端資料庫的連線說明
不要在異構平臺下使用expdp，例如：windows匯出遠端linux/unix下oracle的使用者資料，然後透過ftp上傳到linux/unix，再使用impdp匯入，否則本地匯入會遇到如下報錯而失敗：
[oracle@linux ~]$ impdp shijiazhuang/shijiazhuang DIRECTORY=zhul dumpfile=shijiazhuang20160719.dmp logfile=shijiazhuang20160719_imp.log
Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:28:52 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/home/oracle/shijiazhuang20160719.dmp" may be an original export dump file
[oracle@linux ~]$
下面就說下使用dblink結合expdp匯出遠端庫指定使用者到本地資料庫的方法。
首先，在本地庫建立發起expdp及dblink的使用者，並賦予合適的許可權
[oracle@linux ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 09:29:38 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user zhul identified by zhul;
User created.
SQL> grant dba to zhul;
Grant succeeded.
其次，本地建立dblink
SQL> create public database link orcl10g31
connect to zhul identified by zhul
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl10g)
)
) ';
Database link created.
再次，本地建立expdp客戶端使用的directory路徑
SQL> create directory zhul as '/home/oracle/';
Directory created.
測試dblink對遠端庫的訪問可用性
SQL> select count(*) from scott.emp@orcl10g31;
COUNT(*)
----------
14
SQL>
使用expdp及建立好的dblink匯出遠端庫的指定使用者
[oracle@linux ~]$ expdp zhul/zhul directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31
Export: Release 11.2.0.4.0 - Production on Tue Jul 19 09:35:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ZHUL"."SYS_EXPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows
. . exported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows
. . exported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows
. . exported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows
. . exported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows
Master table "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZHUL.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/shijiazhuang20160819.dmp
Job "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 19 09:35:55 2016 elapsed 0 00:00:30
在本地建立好接收遠端庫指定使用者資料的本地使用者，然後使用impdp匯入遠端庫指定使用者的資料到本地指定使用者下
SQL> create user shijiazhuang identified by shijiazhuang default tablespace shijiazhuang;
User created.
SQL> grant resource to shijiazhuang;
Grant succeeded.
SQL> grant create session to shijiazhuang;
Grant succeeded.
[oracle@linux ~]$ impdp zhul/zhul directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang
Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:36:50 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ZHUL"."SYS_IMPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHIJIAZHUANG" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows
. . imported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows
. . imported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows
. . imported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows
. . imported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ZHUL"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Jul 19 09:37:06 2016 elapsed 0 00:00:15
使用過程中可能遇到的問題：
建立dblink時儘量不要使用tnsname解析，要使用tnsname裡的服務全稱，否則匯出時會遇到如下報錯：
C:\Users\localadmin>expdp zhul/zhul directory=myexpdp dumpfile=shijiazhuang20160
819.dmp logfile=shijiazhuang20160719.log schemas=shijiazhuang network_link=orcl1
0g31
Export: Release 11.2.0.1.0 - Production on 星期二 7月 19 09:15:04 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: 引數值無效
ORA-39200: 連結名稱 "orcl10g31" 無效。
ORA-02019: 未找到遠端資料庫的連線說明
不要在異構平臺下使用expdp，例如：windows匯出遠端linux/unix下oracle的使用者資料，然後透過ftp上傳到linux/unix，再使用impdp匯入，否則本地匯入會遇到如下報錯而失敗：
[oracle@linux ~]$ impdp shijiazhuang/shijiazhuang DIRECTORY=zhul dumpfile=shijiazhuang20160719.dmp logfile=shijiazhuang20160719_imp.log
Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:28:52 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/home/oracle/shijiazhuang20160719.dmp" may be an original export dump file
[oracle@linux ~]$
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2122216/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵在本地匯出資料到遠端資料庫中資料庫
- oracle資料庫遠端不落地匯入本地資料庫Oracle資料庫
- Oracle expdp資料泵遠端匯出Oracle
- expdp遠端匯出資料
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- 遠端資料庫AWR 匯入到本地分析資料庫
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 用exp/imp把遠端資料匯入到本地資料庫中資料庫
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 遠端登入server匯出linux下oracle資料庫中的資料並從本地下載ServerLinuxOracle資料庫
- IMPDP匯入遠端資料庫資料庫
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- oracle 備份資料庫，匯出資料庫Oracle資料庫
- 資料庫遠端檔案匯入資料庫
- 資料庫SQL Server DAC 匯入匯出資料到SQL Azure問題資料庫SQLServer
- MongoDB日常運維-07遠端匯出資料到execlMongoDB運維
- oracle資料庫匯入匯出命令！Oracle資料庫
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- oracle資料庫的impdp，expdpOracle資料庫
- ORACLE 配置連線遠端資料庫Oracle資料庫
- 安裝mongodb，建立資料庫、使用者、建立表、匯出匯入資料庫MongoDB資料庫
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 通過dblink，資料泵expdp遠端跨版本導庫
- [LotusScript] 匯出指定Server下的所有資料庫資訊Server資料庫
- Oracle匯出資料庫與還原Oracle資料庫
- Oracle資料庫的匯入和匯出命令Oracle資料庫
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- oracle按照表條件expdp匯出資料Oracle
- 資料庫的匯入匯出資料庫
- mysql 資料庫匯入匯出MySql資料庫
- MySQL資料庫匯入匯出MySql資料庫
- 遠端資料庫恢復資料庫
- sqoop導oracle資料庫的資料到hiveOOPOracle資料庫Hive
- proc插入資料到資料庫資料庫
- Oracle資料庫——資料匯出時出現匯出成功終止, 但出現警告。Oracle資料庫
- Oracle資料鏈+同義詞+訪問遠端資料庫Oracle資料庫