GRANT SELECT ON Error 'ORA-01917

最新推荐文章于 2024-07-30 08:30:00 发布

原创最新推荐文章于 2024-07-30 08:30:00 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #IBM

Oracle 专栏收录该内容

3 篇文章

订阅专栏

在使用Oracle的imp命令从.dmp文件导入数据到另一数据库时，由于options参数设置错误，特别是'grants=no'，可能会导致'ORA-01917'错误。解决方法主要在于调整导入选项配置。

在从一台Oracle通过imp命令结合*.dmp文件导入到另一个数据库中，由于options参数设置问题，会出现如下问题：

Error 'ORA-01917: user or role 'FTIQA' does not exist' when importing Controller Oracle database

Technote (troubleshooting)

Problem(Abstract)

Oracle DBA tries to import Controller user/schema into Oracle database, using a command similar to the following:

imp <a href="mailto:system/password@databasename">system/password@databasename</a> file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log
fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME

DBA receives error message.

Symptom

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by ADMIN_USERNAME, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing SOURCEUSERNAME's objects into DESTINATION_USERNAME
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_ALS" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error
1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_SM" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_SMD" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_JRNLS_ALS" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered...

Cause

The environment from where the original backup (export / .DMP) file was created is using the (optional) Oracle feature which creates additional grants on objects in the Controller schema.

Resolving the problem

Import the user, but this time use the optional switch grants=no.

Steps:Modify your import script, to something similar to:

imp system/password@databasename file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME grants=no

from IBM

以上一个例子和我遇到的大同小异，上边分析中有原因也有解决方法，仅供参考（其中最主要就是grants=no这个参数）。