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).
Comments
Post a Comment