利用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博客 ” ，链接：https://blog.itpub.net/9932141/viewspace-660152/ ，如需转载，请注明出处，否则将追究法律责任。
