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
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)