利用IMPDP中的NETWORK_LINK功能實現schema資料遷移並解決ORA-31631錯誤
使用IMPDP遷移schema資料一個比較簡便的方式是利用NETWORK_LINK選項實現。
下面是測試環境：
源庫：windows xp oracle 10.2.0.4 AMERICAN_AMERICA.AL32UTF8
目標庫：windows 2003 oracle 10.2.0.4 TRADITIONAL CHINESE_TAIWAN.AL32UTF8
IMPDP的資料來源既可以是EXPDP匯出的DUMP檔案，也可以是NETWORK_LINK指定的資料庫源。SCHEMAS選項指定的要複製的使用者(schema)，REMAP_SCHEMAS選項說明源庫與目標庫的SCHAMA的對應關係。
在目標庫建立獨立測試的使用者及表空間。
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 19 14:02:03 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> create tablespace hrbak
2 datafile 'E:\oradata\auxdb\hrbak.dbf'
3 size 300M
4 autoextend on;
Tablespace created.
SQL> create user hrbak identified by hrbak
2 default tablespace hrbak;
User created.
使使用者具有建立DBLINK的許可權
SQL> grant connect,resource,create database link to hrbak;
Grant succeeded.
建立一個DUMP目錄，並使使用者有讀寫許可權
SQL> create directory ora_dump as 'D:\oraDump';
Directory created.
SQL> grant read,write on directory ora_dump to hrbak;
Grant succeeded.
在使用者環境中建立DBLINK，並測試是否成
SQL> conn hrbak/hrbak;
Connected.
SQL> create database link hrlink connect to hr identified by hr using '//169.254.4.170:1521/orcl';
Database link created.
SQL> set line 180
SQL> select * from jobs@hrlink;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在之前的測試過程中由於未給定IMP_FULL_DATABASE許可權，導致產生ORA-31631
C:\Documents and Settings\F2839647>impdp hrbak/hrbak directory=ora_dump network_
link=hrlink schemas=hr remap_schema=hr:hrbak
Import: Release 10.2.0.4.0 - Production on Monday, 19 April, 2010 14:10:48
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
給使用者HRBAK分配IMP_FULL_DATABASE許可權
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 19 14:11:10 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> grant imp_full_database to hrbak;
Grant succeeded.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
下面還是提示許可權失敗的問題，懷疑是LINK的使用者的問題了
C:\>impdp hrbak/hrbak directory=ora_dump network_
link=hrlink schemas=hr remap_schema=hr:hrbak
Import: Release 10.2.0.4.0 - Production on Monday, 19 April, 2010 14:10:48
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
在源庫中給HR使用者分配一個EXP_FULL_DATABASE許可權，檢視物件的數量
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 19 10:09:13 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> grant exp_full_database to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select count(*) from cat;
COUNT(*)
----------
11
再執行匯入命令，成功！
C:\>impdp hrbak/hrbak directory=ora_dump network_link=hrlink schemas=hr remap_schema=hr:hrbak
Import: Release 10.2.0.4.0 - Production on Monday, 19 April, 2010 14:11:40
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HRBAK"."SYS_IMPORT_SCHEMA_01": hrbak/******** directory=ora_dump netw
ork_link=hrlink schemas=hr remap_schema=hr:hrbak
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HRBAK" 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "HRBAK"."COUNTRIES" 25 rows
. . imported "HRBAK"."DEPARTMENTS" 27 rows
. . imported "HRBAK"."EMPLOYEES" 107 rows
. . imported "HRBAK"."JOBS" 19 rows
. . imported "HRBAK"."JOB_HISTORY" 10 rows
. . imported "HRBAK"."LOCATIONS" 23 rows
. . imported "HRBAK"."REGIONS" 4 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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HRBAK"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 14:13:49
檢視匯入物件的數量
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 19 15:10:33 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn hrbak/hrbak
Connected.
SQL> select count(*) from cat;
COUNT(*)
----------
11
總結
在使用IMPDP工具的NETWORK_LINK選項進行SHEMA匯入時，在許可權的設定要注意，不僅在源庫使用者要具有EXP_FULL_DATABASE許可權，目標庫的匯入還要具有IMP_FULL_DATABASE許可權。
利用NETWORK_LINK可以更加方便地進行移動資料。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-660152/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用impdp的network_link遷移資料庫Oracle資料庫
- 【IMPDP】實現不同使用者之間的資料遷移——REMAP_SCHEMA引數REM
- 解決遷移資料庫錯誤，索引長度過長資料庫索引
- Oracle使用資料泵 (expdp/impdp)實施遷移Oracle
- 使用expdp、impdp遷移資料庫資料庫
- 利用MongoDB的SplitVector命令實現併發資料遷移MongoDB
- expdp/impdp來解決exp/imp出現的錯誤並匯入指定表空間
- 利用sqlldr工具進行資料遷移時發現的問題解決方法SQL
- 【IMPDP】不同資料庫例項不同使用者間資料遷移複製——NETWORK_LINK引數資料庫
- 【IMPDP】同一資料庫例項不同使用者間資料遷移複製——NETWORK_LINK引數資料庫
- exp/imp出現錯誤通過expdp/impdp來解決
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- flask資料庫遷移 No changes in schema detected.Flask資料庫
- 從Windows到Mac遷移Android專案出現錯誤解決方案WindowsMacAndroid
- impdp 遇到 ORA-07445 錯誤。重新EXPDP，IMPDP解決了。
- 使用impdp,expdp資料泵進入海量資料遷移
- 執行impdp時出現ORA-39154錯誤的解決案例
- 遷移過程中出現的open failed錯誤AI
- expdp/impdp使用sysdba許可權遷移資料
- 關於資料庫使用impdp+network_link遷移時，job的遷移資料庫
- Oracle 利用RMAN 完成資料遷移Oracle
- 透過REMAP_SCHEMA引數來實現不同使用者之間的資料遷移REM
- 【實驗】利用可傳輸表空間技術實現資料的高效遷移
- Oracle資料庫遷移之三：dblink+impdpOracle資料庫
- expdp/impdp 使用version引數跨版本資料遷移
- 使用bulkCollect解決資料遷移問題
- 利用PLSQL實現表空間的遷移（一）SQL
- 利用PLSQL實現表空間的遷移（二）SQL
- 利用PLSQL實現表空間的遷移（四）SQL
- 利用PLSQL實現表空間的遷移（三）SQL
- 利用PLSQL實現表空間的遷移（五）SQL
- Larabel遷移檔案時報SQLSTATE[42000]錯誤的解決方法SQL
- 說說框架的資料庫遷移功能框架資料庫
- Long型別的資料,利用COPY命令遷移型別
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- Oracle Expdp/Impdp 進行資料遷移的 幾點注意事項Oracle
- 通過impdp做資料庫遷移遇到的問題總結資料庫
- laravel 資料庫遷移時報錯Laravel資料庫