Getting IMP-00403 warning when using IMP (12.2.0.1.0) but the generated import_sys.sql which failed is empty

I have an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 on Windows 2019 (64bit).

In short, when importing a dump file I’m getting the following:

IMP-00403: Warning: This import generated a separate SQL file “import_sys” which contains DDL that failed due to a privilege issue.

The full details of the scenario:

Step 1: sqlplus as sysdba:

CREATE TABLESPACE TEST_1 datafile 'D:/DB_DATA/orcl/TEST_1.DBF' SIZE 1G AUTOEXTEND ON MAXSIZE 31G;
CREATE USER TEST_1 IDENTIFIED BY pwd DEFAULT TABLESPACE TEST_1 QUOTA UNLIMITED ON TEST_1;
GRANT CONNECT TO TEST_1;
GRANT RESOURCE TO TEST_1;
GRANT EXP_FULL_DATABASE TO TEST_1;
GRANT IMP_FULL_DATABASE TO TEST_1;

Step 2: sqlplus as TEST_1:

CREATE TABLE TEST_1.test_table (field_1 VARCHAR2(10) NOT NULL, field_2 VARCHAR2(10) NOT NULL);
CREATE UNIQUE INDEX TEST_1.i_test_table ON TEST_1.test_table (field_1);
INSERT INTO TEST_1.test_table (field_1,field_2) VALUES ('A','B');
COMMIT;

Step 3: from cmd:

exp TEST_1/pwd@orcl TABLES=(TEST_1.test_table) DIRECT=Y FILE=c:test_table.dmp

Step 4: sqlplus as TEST_1:

DROP TABLE TEST_1.test_table PURGE;

Step 5: from cmd:

imp TEST_1/pwd@orcl TABLES=(test_table) FROMUSER=TEST_1 TOUSER=TEST_1 FILE=c:test_table.dmp

The table actually imports but I get the warining mentioned. Every forum says it’s privilege issue and should check the sql file but the file generated is empty. I have also tried granting SYS and SYSDBA to the user as well as trying to do the import with the SYSDBA account itself – always get the same result. I have installed another Database but instead 12.2 I installed 12.1 and carrying out the exact same steps I do NOT get the warning in 12.1.

Please don’t advise me to use impdp instead of imp or ignore the warning etc. This is the exact problem and this is what I need to solve – no upgrade, no downgrade.

I have compared the logs and the dump file to the one generated by 12.1 (which works) – I see no difference at all. I suspect this is an issue with IMP itself but there’s got to be a solution.

Any ideas?

Answer

From the documentation:

Note: As of Oracle Database 12c release 2 (12.2) the import utility (imp), for security reasons, will no longer import objects as user SYS. If a dump file contains objects that need to be re-created as user SYS, then the imp utility tries to re-create them as user SYSTEM instead. If the object cannot be re-created by user SYSTEM, then you must manually re-create the object yourself after the import is completed.

If the import job is run by a user with the DBA role, and not all objects can be re-created by user SYSTEM, then the following warning message is written to the log file:

IMP-00403:
Warning: This import generated a separate SQL file "logfilename_sys" which contains DDL that failed due to a privilege issue.

The SQL file that is generated contains the failed DDL of objects that could not be re-created by user SYSTEM. To re-create those objects, you must manually execute the failed DDL after the import finishes.

The warning is being generated even though there is actually nothing to be done, which you can see from the empty file.

My Oracle Support Doc ID 2298963.1 refers to this and says:

If the file is empty, no objects needs to be recreated manually.

As you’ve checked the file and it was empty, and there were no other warnings or errors, you can ignore this (spurious) warning. It seems to be a minor – if annoying – bug that it reports the warning when it doesn’t need to, when there are no relevant objects using the definition above.

To avoid it, run the import as a user that does not have the DBA role. At least, that’s what the docs say… but your user doesn’t have that role anyway. It appears the restriction is actually related to the exp_full_database and/or imp_full_database roles, which your user does have, and which are granted to DBA (by catexp.sql). That kind of makes sense given how powerful those roles are, but it’s unhelpful that the documentation in confusing. (It says the same in the 19c docs; I’ve given Oracle feedback, referring back to your question.)

In your example you don’t need the exp_full_database or imp_full_database roles, since you are only operating on a single schema anyway. If you revoke those roles the warning should go away.