Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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!

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.