Troubleshooting
Problem
Oracle DBA tries to import Controller user/schema into Oracle database, using a command similar to the following: imp system/password@databasename 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
Delete the schema, then re-create a blank schema. Finally, import the user (into the new schema) 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
Historical Number
1039437
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21371533