With Oracle Datapump, traditional export and import tools has faded out because Datapump has better performance and rich features. Still, exp/imp is supported and used for small tasks.

I’ve run into the following error when using imp to moving a SQL baseline between two 19c databases:

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

The reason was since Oracle 12c release 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 above warning message is written to the log file.

imp will create a SQL file with the name specified for the LOG parameter and appended with “_sys.sql”. For example, if you specify “log=imp_basline.log”, the SQL file name will be “imp_basline_sys.sql”. If no log file was specified, then the default name of the SQL file is import_sys.sql.

It may 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.

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

• Oracle