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 expdp資料泵遠端匯出Oracle
- oracle 備份資料庫，匯出資料庫Oracle資料庫
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- oracle資料庫的impdp，expdpOracle資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- oracle按照表條件expdp匯出資料Oracle
- ORACLE 配置連線遠端資料庫Oracle資料庫
- 通過dblink，資料泵expdp遠端跨版本導庫
- Oracle匯出資料庫與還原Oracle資料庫
- MongoDB日常運維-07遠端匯出資料到execlMongoDB運維
- Oracle資料庫——資料匯出時出現匯出成功終止, 但出現警告。Oracle資料庫
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- PL/SQL Developer連線遠端Oracle資料庫SQLDeveloperOracle資料庫
- Access 匯入 oracle 資料庫Oracle資料庫
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- expdp 匯出時指定節點
- Mysql 資料庫匯入與匯出MySql資料庫
- git 從遠端倉庫指定分支clone程式碼到本地Git
- 遠端連線mysql資料庫MySql資料庫
- SQL資料庫的匯入和匯出SQL資料庫
- MySQL匯出資料庫指令碼MySql資料庫指令碼
- SQLServer匯出匯入資料到MySQLServerMySql
- Oracle 資料匯入匯出Oracle
- Oracle資料匯入匯出Oracle
- 資料庫遠端連線失敗資料庫
- 將資料庫中資料匯出為excel表格資料庫Excel
- python將目標檢測資料匯入到指定資料庫中Python資料庫
- mysql dump 拉取遠端資料同步到本地庫的shell 指令碼MySql指令碼
- Sqoop匯出ClickHouse資料到HiveOOPHive
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- Oracle12c資料庫連結匯出與刪除(DatabaseLink)Oracle資料庫Database
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- oracle資料庫使用者建立步驟Oracle資料庫
- 本地oracle資料庫忘記使用者名稱密碼解決方案Oracle資料庫密碼
- oracl 資料庫 sqlplus 匯出資料為sql檔案資料庫SQL
- 使用Xmanager軟體遠端呼叫圖形化安裝Oracle資料庫Oracle資料庫
- (詳細)ubuntu18.04建立mysql資料庫並本地database遠端連線UbuntuMySql資料庫Database
- git 修改本地倉庫的遠端倉庫地址Git