impdp does not create user/schema impdp does not create user/schema sql sql

impdp does not create user/schema


So I figured it out on my own.

If the schema you are remapping 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. Although I'm not remapping as such, export part is relevant as my user (ZABBIX) was a basic user and not DBA/create-user-privileged.

I did 'GRANT CREATE USER TO ZABBIX' in my case, ran the export again and this time some additional "object types" were processed.

OLD OUTPUT when zabbix user didn't have the 'CREATE USER' privilege:

(0)oracle@sourceHOST$ expdp zabbix/zabbix schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log.......Starting "ZABBIX"."SYS_EXPORT_SCHEMA_01":  zabbix/******** schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.logProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSMaster table "ZABBIX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************.......

NEW OUTPUT:

(0)oracle@sourceHOST$ expdp zabbix/zabbix schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log.......Starting "ZABBIX"."SYS_EXPORT_SCHEMA_01":  zabbix/******** schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.logProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSMaster table "ZABBIX"."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.

Much simpler solution: Use 'system' to run expdp and impdp. It has its own pitfalls, but for my case that's the best.