Examine these Data Pump commands to export and import objects from and to the same
database.
The dba has not yet created users hr1 and oe1.
$expdp system/manager
schemas = hr.oe
directory = EXP_DIR
dumpfile = export.dat
include = table
$ impdpsysten/manager
schemas = hr1,oe1
directory = EXP_DIR
dumpfile = export.dat
remap_schena=hr:hrl, oe:oe1
What will happen when running these commands?
A.
expdp will fail because no path has been defined for the dumpfile.
B.
expdp will succeed but impdp will fail because the users do not exist.
C.
inpdp will create two users called hr1 and oe1 and import all objects to the new schemas.
D.
impdp will create two users called hr1 and oe1 and import only the tables owned by hr and
oe schemas to ht1 and oe1 schemas, respectively.
and of course this is also WRONG! someone is trying to sabotage our exams…
The correct answer should be B. This same question is from the 1z0-040 exam (Oracle Database 10g: New Features for Adminsitrators).
If user hr1 and oe1 already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr & oe schema into the existing hr1 and oe1 schema.
If user hr1 and oe1 does not exist before you execute the import operation, then Import automatically creates it
https://docs.oracle.com/database/121/SUTIL/GUID-619809A6-1966-42D6-9ACC-A3E0ADC36523.htm#SUTIL927
C for me is fine
Correct answer is “C” only.
B (if expdb use include = table) my impdb fail with
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:”HR1″.”T1″ failed to create with error:
ORA-01918: user ‘HR1’ does not exist
but without clause “include = table” everything Ok impdb created new users
B, I tested.
Yes B is correct ,i have tested it.
ORA-39002: invalid operation
ORA-39165: Schema OE1 was not found.
ORA-39165: Schema HR1 was not found.
D
http://docs.oracle.com/cd/B14117_01/server.101/b10825/dp_import.htm#sthref324
If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema.
It’s B. I tested also.
Thanks Vonpire!
I think C
Export: Release 12.1.0.2.0 – Production on Wed Dec 30 16:39:45 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/********@pdborcl schemas=hr,oe directory=test_pump dumpfile=emp2.dmp logfile=expdp_emp2.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 49.25 MB
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/TABLESPACE_QUOTA
…
Export Succeeded!
And import has some errors but yes all tables were imported
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “HR2″.”TRADE” 8.521 MB 73641 rows
. . imported “HR2″.”LIST_HASH_TAB”:”PART_AA”.”PART_AA_01″ 6.593 KB 7 rows
. . imported “HR2″.”LIST_HASH_TAB”:”PART_AA”.”PART_AA_02″ 6.828 KB 13 rows
. . imported “HR2″.”LIST_HASH_TAB”:”PART_BB”.”PART_BB_01″ 6.710 KB 10 rows
. . imported “HR2″.”LIST_HASH_TAB”:”PART_BB”.”PART_BB_02″ 6.710 KB 10 rows
. . imported “OE2″.”PRODUCT_DESCRIPTIONS” 2.379 MB 8640 rows
. . imported “OE2″.”WAREHOUSES” 12.75 KB 9 rows
. . imported “HR2”.”TEST1″ 5.054 KB 1 rows
Hi everyone.
I found the problem.
In the question. It has parameter: include = table so we need to create user hr1 and oe1 before import.
If no parameter include = table then there is no need to create user hr1 and oe1 before importing.
So the answer is b. Trick, trick trick be carefull :)))
You are correct
B
I agree with D as per oracle documentation.
The question is tricky
My answer is D.
Based on this
“If the schema you are remapping to does not already exist, then the import operation creates it, provided that the dump file set contains the necessary CREATE USER metadata for the source schema, and provided that you are importing with enough privileges. For example, the following Export commands create dump file sets with the necessary metadata to create a schema, because the user SYSTEM has the necessary privileges:”
Notice that the user is System.
The answer is B as the EXPDP with Include=TABLE will only create an export dump with the metatdata and data of all tables in the HR and OE schemas. Since the dumpfile created above doesn’t contain the ddl for the create schema but only the table related stuff so the impdp will not be able to create the new users. That’s why both expdp and impdp work if you remove the include=table because then the expdp exports everything in those schemas along with the ddl for those schemas and it uses this to create the new users.
IF you did a impdp with the sqlfile on export.dat generated by
expdp system/m schemas=hr,oe directory=EXP_DIR dumpfile=export.dat include=table and looked at the content for the ddl you will see only the ddl fro the tables are there. But if you did the same for one without the include=table parameter you will see create user statements for hr and oe user .
This is indeed tricky but I agree with B.
as include=TABLE cannot create the schema which does not exist as the dump file does not have it so the impdp will fail in this case.
B is correct. I agree sail_96 and cns
“user not created” and “Include=table”
I tested too.
impdp fail
ORA-39165: Schema OE1 was not found.
ORA-39165: Schema HR1 was not found.
B
ORA-39165: Schema not found error will be resulted
B
tested
include=TABLE is a key
B