Impdp does not created user/schema

Until  now my understanding for EXPDP/IMPDP was that if the user performing import has 'IMPORT FULL DATABASE' privilege, it'll create the users/schemas in the target database. I needed to export (expdp) schema from one machine and import (impdp) it to another machine. I just wanted  impdp to create schema on target database.

While doing  import  i encountered error

$ impdp system/oracle123 directory=TEST_DIR dumpfile=test.dmp logfile=impdptest.log

 .......

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=impdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:

ORA-31625: Schema USR_TEST is needed to import this object, but is unaccessible

ORA-01435: user does not exist

Failing sql is:

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'XXX.YYY.COM', inst_scn=>'7788478540892');COMMIT; END;

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"USR_TEST"."TABLE1" failed to create with error:

ORA-01918: user 'USR_TEST' does not exist

Failing sql is:

CREATE TABLE "USR_TEST"."TABLE1" ("COLUMN1" VARCHAR2(20 BYTE) NOT NULL ENABLE, "COLUMN2" VARCHAR2(20 BYTE), "COLUMN3" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAU

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s).

$

After reading some documents,blogs I discovered  that If  the schema you are importing to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.

Meaning the oracle user that exported the schema, should have had CREATE USER privilege.Export was done with user (USR_TEST) was a basic user and not DBA/create-user-privileged. 

Below query given result as.

SQL>  select  *  from  ROLE_SYS_PRIVS  where  PRIVILEGE = ’CREATE USER’ ;

ROLE                                              PRIVILEGE                     ADM
----------------------------           --------------------              ---

DBA                                               CREATE USER                 YES

IMP_FULL_DATABASE           CREATE USER                  NO

2 rows selected.

It means EXP_FULL_DATABAS role does not have create user privilege.

I did 'GRANT CREATE USER TO USR_TEST' here and  ran the export again and this time some additional "object types" were processed.

 when USR_TEST user didn't have 'CREATE USER' privilege:

 $ expdp USR_TEST/USR_TEST schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

.......

Starting "USR_TEST"."SYS_EXPORT_SCHEMA_01":  USR_TEST/******** schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Master table "USR_TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

.......

when USR_TEST user  have 'CREATE USER' privilege:

 $ expdp USR_TEST/USR_TEST schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

.......

Starting "USR_TEST"."SYS_EXPORT_SCHEMA_01":  USR_TEST/******** schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Master table "USR_TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

.......

Note the Processing object type SCHEMA_EXPORT/USER, SCHEMA_EXPORT/SYSTEM_GRANT, SCHEMA_EXPORT/ROLE_GRANT, SCHEMA_EXPORT/DEFAULT_ROLE.

To avoid such situations always try to take the export using SYSTEM user.