同事进行数据库的数据导入操作,下面是相应的数据环境和说明:

导出的用户: abc, 默认表空间OA_INFO, 数据库9208

SQL> select * from user_role_privs;

USERNAME                    GRANTED_ROLE                   ADM DEF OS_
--------------------------- ------------------------------ --- --- ---
ABC                         CONNECT                        NO  YES NO
ABC                         DBA                            NO  NO  NO
ABC                         EXP_FULL_DATABASE              NO  YES NO
ABC                         IMP_FULL_DATABASE              NO  YES NO
ABC                         RESOURCE                       NO  YES NO

要导入的用户: arc_abc, 默认表空间ARC_ABC, 数据库9205

SQL> select * from user_role_privs;

USERNAME                    GRANTED_ROLE                   ADM DEF OS_
--------------------------- ------------------------------ --- --- ---
ARC_ABC                     CONNECT                        YES NO  NO
ARC_ABC                     DBA                            NO  NO  NO
ARC_ABC                     EXP_FULL_DATABASE              NO  NO  NO
ARC_ABC                     IMP_FULL_DATABASE              NO  NO  NO
ARC_ABC                     RESOURCE                       YES NO  NO
PUBLIC                      PLUSTRACE                      NO  YES NO

导入时出现:
Import: Release 9.2.0.5.0 - Production on Wed May 21 09:33:54 2008
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Username: arc_ABC
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Import file: expdat.dmp > abcmove_20080517.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

一阵瞎操作后,发现user_role_privs(default_role)这一字段上的输出不一样,
abc(CONNECT/RESOURCE/EXP_FULL_DATABASE/IMP_FULL_DATABASE)的,
arc_abc(PLUSTRACE)的, abc和arc_abc激活的权限是完全不同的!!!

使用命令 alter user arc_abc default role all 进行激活,重新进行数据导入,
一切OK.

相关的语法:
alter user USERNAME default role all [ except ROLENAME];