Why is my create user missing from the schema dumpfile?



When exporting a schema sometimes it may appear that the 'CREATE USER' command is sometimes included and other times is missing. Initially you may think this is a bug but it's actually a 'feature'. Even exporting your own schema does not include the command unless you have the correct rights in the database - see the example below where we only give a small set of rights.

SYS@>create user demo identified by demo;

User created.

SYS@>grant connect,resource to demo;

Grant succeeded.

SYS@>grant all on directory data_pump_dir to demo;

Grant succeeded.






If we now export our own user it looks to work fine - and indeed it does - but the user part is missing if you look at the list of exported objects

[oracle@]::/oracle/export/# impdp demo/demo network_link=demo schemas=demo sqlfile=demo.sql directory=data_pump_dir

Import: Release 11.2.0.3.0 - Production on Sat Jan 4 22:11:08 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "DEMO"."SYS_SQL_FILE_SCHEMA_01":  demo/******** network_link=demo schemas=demo sqlfile=demo.sql directory=data_pump_dir
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DEMO"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 22:11:13


This line is missing.....

Processing object type DATABASE_EXPORT/SCHEMA/USER

And we are therefore missing these commands from the dumpfile,

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER

 CREATE USER "DEMO" IDENTIFIED BY VALUES 'S:FDD88ACD715F096D363880AF2990B1B442BB871B42BA3AEDACA9FB13D4CB;4646116A123897CF'
      TEMPORARY TABLESPACE "TEMP";


To get the create user included we have to do the export as a user with the correct rights, either DATAPUMP_EXP_FULL_DATABASE or DBA of course. A subset of rights may also make this work - I've not actually gone and found out what the actual cause is.

This is oof course still way better than old exp/imp where create user was always missing (unless doing a complete full export).

0 comments:

Post a Comment