Oracle “Import from Export Files” screen refusing both SYSADMIN and Normal users

Question

I have just installed Oracle 11g on Win64, and I want to import some .dmp files.

I am trying with the tool Oracle Enterprise Manager 11g:

• If I log in with SYS as SYSADMIN, and go to Import from Export Files, I am told Role Error. Export and Import are not supported on 10g databases for users logged in with the SYSDBA role. Logout and login using a different role before trying again.

• If I log in with SYSMAN as Normal, the Import from Export Files screen shows no error, but when I click Create Directory Object then OK, I get: Errors. Failed to commit: ORA-01031: insufficient privileges. You are not logged in with SYSDBA privilege. You cannot assign privileges to directory object.

I tried creating a "Directory Object" as Normal, but unfortunately it is not visible under SYSADMIN.

How can I use this Import from Export Files screen ?

Answer 1

The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even granting an additional role, unless specifically told to by Oracle.

The first message you got says that import isn't supported when logged in with the SYSDBA role. That doesn't mean you can't import as SYS, just that you have to log in as SYS with the NORMAL role rather than as SYSDBA.

This assumes you want to do a full import though; for partial (e.g. schema-level) imports you might want to create the user first and perform the import as that user, but I'm not sure that's what you're looking for at the moment.

Answer 2

The best you can do is the following

SQL> create or replace directory DIR_TO_IMP as '/u01/export01';

Directory created.

SQL> grant read , write on directory DIR_TO_IMP to scott;

Grant succeeded.

Answer 3

I ended up manually granting directory creations rights to sysman:

SQL> GRANT CREATE ANY DIRECTORY TO sysman;

Then directory creation worked.

Note: This would be very risky in production. Any better solution is very welcome!