Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Adel.

Asked: May 27, 2020 - 9:58 am UTC

Answered by: Connor McDonald - Last updated: May 30, 2020 - 7:44 am UTC

Category: Database Administration - Version: 12.2C

Viewed 100+ times

You Asked

Hello Team
a while ago, I had a problem with a DB import. I managed to get it, but there's a question still remain in my head

1. what is the difference between

expdp system/system schemas=ieulive directory=dir1 dumpfile=dump1.dmp logfile=exp.log
impdp system/system remap_schema=ieulive:ieulive2 directory=dir2 dumpfile=dump1.dmp logfile=imp.log


and

expdp ieulive/ieulive directory=dir1 dumpfile=dump1.dmp logfile=exp.log

sql>grant connect, resource, dba to ieulive2 identified by ieulive2;

impdp ieulive2/ieulive2 directory=dir2 dumpfile=dump1.dmp logfile=imp.log


2. can I use them interactively, like:
expdp system/system schemas=ieulive directory=dir1 dumpfile=dump1.dmp logfile=exp.log
impdp ieulive2/ieulive2 directory=dir2 dumpfile=dump1.dmp logfile=imp.log

or
expdp ieulive/ieulive directory=dir1 dumpfile=dump1.dmp logfile=exp.log
impdp system/system remap_schema=ieulive:ieulive2 directory=dir2 dumpfile=dump1.dmp logfile=imp.log


3. the situation is this same when adding
full=y

right?

Hope you got my question
Regards

and we said...

They are different. For example, I exported the SCOTT schema, and then tried to import that connecting as DEMO

C:\temp>impdp userid=demo/demo@db19_pdb1 dumpfile=SCOTT.DMP directory=temp

Import: Release 19.0.0.0.0 - Production on Thu May 28 11:07:21 2020
Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39154: Objects from foreign schemas have been removed from import
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  userid=demo/********@db19_pdb1 dumpfile=SCOTT.DMP directory=temp
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Thu May 28 11:07:27 2020 elapsed 0 00:00:04


It *looks* successful, but then I do this:

SQL> conn demo/demo@db19_pdb1
Connected.

SQL> select * from obj;

no rows selected



There was nothing there because I looked for things belonging to DEMO and there were none in the dump.

Ultimately I still need the same REMAP_SCHEMA parameter, no matter who I am connecting as

C:\temp>impdp userid=demo/demo@db19_pdb1 dumpfile=SCOTT.DMP directory=temp remap_schema=scott:demo

Import: Release 19.0.0.0.0 - Production on Thu May 28 11:09:13 2020
Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  userid=demo/********@db19_pdb1 dumpfile=SCOTT.DMP directory=temp remap_schema=scott:demo
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."EMP"                                8.773 KB      14 rows
. . imported "DEMO"."DEPT"                               6.023 KB       4 rows
. . imported "DEMO"."SALGRADE"                           5.953 KB       5 rows
. . imported "DEMO"."BONUS"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Thu May 28 11:09:27 2020 elapsed 0 00:00:14


In all cases, you can use interactive mode.

Full data pump needs a DBA privilege (or the underlying full export/import privs)

and you rated our response

  (2 ratings)

Reviews

Question

May 28, 2020 - 11:58 am UTC

Reviewer: Adel from Baghdad, Iraq

that a was helpfull, but the question came down to this:
its not important at impdp
at expdp is there a recommended, or faster method, or favorite one.
between exporting using system user, or the user itself?

expdp system/system schemas=ieulive

expdp ieulive/ieulive

what one way would give that other one will not?

ps: ieulive is a DBA
Connor McDonald

Followup  

May 30, 2020 - 7:44 am UTC

Minimal difference if any

Question

May 28, 2020 - 12:00 pm UTC

Reviewer: Adel from Baghdad, Iraq

that a was helpfull, but the question came down to this:
its not important at impdp
at expdp is there a recommended, or faster method, or favorite one.
between exporting using system user, or the user itself?

expdp system/system schemas=ieulive

expdp ieulive/ieulive

what one way would give that other one will not?

ps: ieulive is a DBA

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.