This content has been marked as final. Show 6 replies
Pl post details of OS and database versions.
EXP_FULL_DATABASE and IMP_FULL_DATABASE are powerful roles and should be granted with care. Without these roles, users can only export/import schemas they have access to.
I think your statement “Without these roles, users can only export/import schemas they have access to.” Is false because I tried to export the cm_test schema using the cm_test user ID and password and it failed, see below.
expdp cm_test/cm_test@local schemas=cm_test directory=SR_BACKUP dumpfile=CM_TEST.dmp logfile=SR_TEST_export.log exclude=STATISTICS
Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "CM_TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01031: insufficient privileges
The OS is windows 7 Prof.
Pl identify the privileges/roles that this CM_TEST schema has been granted. This MOS Doc may help
Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
I don't know what the minimum set of privs are, but I test all the time with
grant connect,resource, unlimited tablespace to user identified by user;
I'm not sure if you can eliminate any of these and still have Data Pump work for the schema that is running the Data Pump job.
For IMPORT, Whatever objects User need to import, that user should have privileges to create those objects by having privilege “ CREATE <OBJECT TYPE > to import in its own schema itself.
Eg: CREATE TABLE, CREATE TRIGGER,CREATE PROCEDURE...etc
For EXPORT, only CREATE SESSION privileges is enough to export from its own schema.
Minimum is CONNECT & RESOURCE is enough for doing export and import within own schema. To another schema you should need authentication in other schema objects or DBA role
Edited by: user12043395 on Feb 26, 2013 12:18 PM
Just need to GRANT DATAPUMP_EXP/IMP_FULL_DATABASE role in 11g or EXP/IMP_FULL_DATABASE full database
and do not forget after create the user set as defalut role:
ALTER USER DEFAULT ROLE ALL;