数据泵报错ORA-39149

在利用NETWORK_LINK方式导出的时候，出现了这个错误。

详细错误信息如下：

bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release11.1.0.6.0 - 64bit Production on星期二, 16 9月, 2008 17:08:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit Production
/D @[Z,g1M
a0With the Partitioning, OLAP, Data Mining and Real Application Testing options
hNWPgl0ORA-31631:需要权限ITPUB个人空间(CKR`{D1^
ORA-39149:无法将授权用户链接到非授权用户

检查Oracle的错误手册：

ORA-39149: cannot link privileged user to non-privileged userITPUB个人空间
j }4B-x+Pu)g2A
Cause: A Data Pump job initiated be a user with EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE roles specified a network link that did not correspond to a user with equivalent roles on the remote database.
l$\_9F{0Action: Specify a network link that maps users to identically privileged users in the remote database.

错误描述的比较清楚，不过这个错误很难理解，难道一个权限大的用户不能通过数据库链导出一个权限小的用户。

当然，了解了这个错误的原因，其实问题很容易解决。在本地创建一个新用户，不要授权EXP_FULL_DATABASE/IMP_FULL_DATABASE角色，就可以导出：

bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release11.1.0.6.0 - Production on星期二9月16 16:53:48 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

R4iyg}#@hbQ0连接到:
s Vk[{7t0Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit ProductionITPUB个人空间ls5L-|)im/c
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col grantee format a15ITPUB个人空间zO;w$K%R(J)V}
SQL> col granted_role format a15
8aL3@L.Z~1]x0SQL> select grantee, granted_role from dba_role_privs
2[!l@
@,h0  2  where grantee = 'YANGTK';

GRANTEE         GRANTED_ROLE
+C XSe5f4P G0--------------- ---------------ITPUB个人空间FUEb9Ip8b,p+W9|3{
YANGTK          CONNECT
)R+q&e3xB3~0YANGTK          RESOURCEITPUB个人空间:RA6s6G(P:jR/pF*s#K
YANGTK          DBA

SQL> drop user test cascade;

用户已删除。

SQL> create user test identified by test
Y.iS
\3TB?NN\0  2  default tablespace users
8[U k-lB?}9u!| rO0  3  quota unlimited on users;

用户已创建。

SQL> grant connect to test;

授权成功。

SQL> grant create table, create database link to test;

授权成功。

SQL> grant read, write on directory d_temp to test;

授权成功。

SQL> conn test/testITPUB个人空间6T?!S
Z(m2j7?bh
已连接。ITPUB个人空间%O2\-["x$J EP
SQL> create database link test113 connect tojiangsuidentified byjiangsuITPUB个人空间sv-^&vc
  2  using '172.0.2.113/test';

数据库链接已创建。

SQL> select * from global_name@test113;

GLOBAL_NAMEITPUB个人空间-r_H/k9W6I hS
--------------------------------------------------------------------------------
9?J7v&f6Rv2p0TEST

SQL> exit
%K
C"K#RD4g
@,I1yF"}0从Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit Production
P5zAp2L8nY0With the Partitioning, OLAP, Data Mining and Real Application Testing options断开

使用这个用户就执行导出了：

bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release11.1.0.6.0 - 64bit Production on星期二, 16 9月, 2008 17:08:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit Production
aoc%X;v'n0With the Partitioning, OLAP, Data Mining and Real Application Testing options
(z bU e:O;JW&|5r`)N6d0ORA-31631:需要权限
9Y{J$C5ktR'v5Lv0ORA-39149:无法将授权用户链接到非授权用户

bash-3.00$ expdp test/test directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release11.1.0.6.0 - 64bit Production on星期二, 16 9月, 2008 17:09:10

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit ProductionITPUB个人空间l Z)jdC
With the Partitioning, OLAP, Data Mining and Real Application Testing options
kr6_*B&[3D0启动"TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org
v c(ARpQ+w0正在使用BLOCKS方法进行估计...ITPUB个人空间6KJ%z#_ rC+TQ;~
处理对象类型TABLE_EXPORT/TABLE/TABLE_DATA
D s[fn'^dO9\4{0使用BLOCKS方法的总估计: 6 MB
-y1ek}X6a6N0处理对象类型TABLE_EXPORT/TABLE/TABLEITPUB个人空间/}y.M,`5~db(O
处理对象类型TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTITPUB个人空间kh
xQ7Q
yv*x#B
处理对象类型TABLE_EXPORT/TABLE/INDEX/INDEXITPUB个人空间[4J2{Uj]
处理对象类型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
a Q3~C5o]0处理对象类型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSITPUB个人空间l$CmZ#U4M
处理对象类型TABLE_EXPORT/TABLE/COMMENT
8w7^yg%q*W&e0处理对象类型TABLE_EXPORT/TABLE/TRIGGER
RL!Q![A+XH0处理对象类型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSITPUB个人空间:XvQ;n4Mu@.A
. .导出了"JIANGSU"."CAT_ORG"                         4.390 MB   31827行ITPUB个人空间0Oh|]0w
已成功加载/卸载了主表"TEST"."SYS_EXPORT_TABLE_01"
8}hQ
nBc,a0******************************************************************************
8I PUm4|5s'Y0TEST.SYS_EXPORT_TABLE_01的转储文件集为:ITPUB个人空间Q4{2^sP3C~|NL
  /data/jiangsu.dp
7\QWJk&P+Y2@0作业"TEST"."SYS_EXPORT_TABLE_01"已于17:09:57成功完成

如果可以修改远端的用户，那么更简单，只需要给远端用户授权EXP_FULL_DATABASE角色就可以了：

-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release10.2.0.3.0 - Production on Tue Sep 16 17:16:27 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ITPUB个人空间$ES0|-E{/T
Connected to:
(w4O.b:j$~4plY0Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit ProductionITPUB个人空间t8G:d:|-fO
With the Partitioning and Data Mining options

SQL> select global_name from global_name;

GLOBAL_NAMEITPUB个人空间Hw+QEb1Lb2h
--------------------------------------------------------------------------------ITPUB个人空间2E1^zBS6w8q9@
TEST

SQL> grant exp_full_database tojiangsu;

Grant succeeded.

下面再次使用yangtk执行导出：

bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu1.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release11.1.0.6.0 - 64bit Production on星期二, 16 9月, 2008 17:19:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database11gEnterprise Edition Release11.1.0.6.0 - 64bit ProductionITPUB个人空间Z(~a:e
YL#DO/Kk$@
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4^5Z8m X}*}"u5P0启动"YANGTK"."SYS_EXPORT_TABLE_01":  yangtk/******** directory=d_temp dumpfile=jiangsu1.dp network_link=test113 logfile=jiangsu.log tables=cat_org
,l2},L&E(I(r0正在使用BLOCKS方法进行估计...ITPUB个人空间(GR-D*@*t&HRIa
处理对象类型TABLE_EXPORT/TABLE/TABLE_DATA
E3j&uq8M/DF&U i1{0使用BLOCKS方法的总估计: 6 MBITPUB个人空间A)K2u\[
处理对象类型TABLE_EXPORT/TABLE/TABLEITPUB个人空间Q*zC Ed!JcZ} B8|z
处理对象类型TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
3h:R1z5DUP:uM0处理对象类型TABLE_EXPORT/TABLE/INDEX/INDEXITPUB个人空间6h`2Z~L[b!g+];gHm
处理对象类型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTITPUB个人空间T!}v
IYTC VI$t
处理对象类型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSITPUB个人空间6o@fT9e7m
处理对象类型TABLE_EXPORT/TABLE/COMMENTITPUB个人空间&X/U[1a R.k]+d
处理对象类型TABLE_EXPORT/TABLE/TRIGGERITPUB个人空间!C9mW;U1?o.y/F'TAu
处理对象类型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSITPUB个人空间Z
d_|'rI,n{9|w
. .导出了"JIANGSU"."CAT_ORG"                         4.390 MB   31827行
+e-`*BB9T9E(H0已成功加载/卸载了主表"YANGTK"."SYS_EXPORT_TABLE_01"
/}/Q3`)k:v/k!p0******************************************************************************
nTcD1W3@0YANGTK.SYS_EXPORT_TABLE_01的转储文件集为:
[#m[p^
T0  /data/jiangsu1.dp
a9X,KTD,[a0作业"YANGTK"."SYS_EXPORT_TABLE_01"已于17:19:45成功完成

问题倒是很容易解决，只是不理解Oracle为什么处理不了这么简单的问题。而且关键的是，Oracle似乎没有把这个问题当作bug。