I am trying to use the Oracle imp utility. A lot of good documentation exists but it seems that it is not possible or documented how to import with a regular user. It is assumed that sys will be used I think. Grant privileges for example using something like
GRANT execute ON sys.dbms_ddl TO myuser;
Which is suggested on some non-Oracle sites. Does not work. The error remains the same.
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DDL", line 361
What grants should I issue to authorize a user for imp utility?
Importing as SYSTEM also does not work. This user also does not have permissions to import.
[vagrant@jira ~]$ imp SYSTEM/oracle@jira file=/tmp/jira-oracle.dmp fromuser=jira touser=jira_restore
Import: Release 12.2.0.1.0 - Production on Tue Oct 9 12:24:09 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V12.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
Has anyone used these utilities with some success? I don't think they are officially supported by Oracle.
They are shipped by Oracle strange enough. On Oracle website
Instant client tools provides exp / imp utilities since v12.2.0.1.0. Unrestricted download is available
But also on the documentation website of Oracle
Original export is desupported for general use as of Oracle Database 11g
So it seems it can be both: distribute tools but not support them!
So I am reaching a conclusion here. Simple export / import of a schema is not so simple if at all possible when using Oracle. Contrary to statements made.
