Skip to Main Content

Breadcrumb

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.