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.