使用Data Pump导出和导入在oracle数据库之间移动数据

使用Data Pump 导出/导入

1. grant dba access to the source schema user and target schema user with sysdba
SQL> grant dba to artem identified by artem;
SQL> grant dba to ivana identified by ivana;

2. create one source table with index in schema artem
SQL> create table artem.users as select * from all_users;
SQL> create index artem.ui on artem.users(user_id);

3. check the status of DATA_PUMP_DIR and create a new one if not exist
SQL> select directory_path from dba_directories where directory_name=‘DATA_PUMP_DIR’;

DIRECTORY_PATH
/u01/app/oracle/admin/orcl/dpdump/

4. execute export command with schema artem
$ expdp artem/artem schemas=artem dumpfile=artem.dmp

Export: Release 12.1.0.2.0 - Production on Mon Jan 28 16:29:47 2019

Copyright © 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 and Real Application Testing options
Starting “ARTEM”.“SYS_EXPORT_SCHEMA_01”: artem/******** schemas=artem dumpfile=artem.dmp
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported “ARTEM”.“USERS” 7.929 KB 39 rows
Master table “ARTEM”.“SYS_EXPORT_SCHEMA_01” successfully loaded/unloaded

---

Dump file set for ARTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/artem.dmp
Job “ARTEM”.“SYS_EXPORT_SCHEMA_01” successfully completed at Mon Jan 28 16:31:44 2019 elapsed 0 00:01:48

5. check the pump file into pump directory
$ ls /u01/app/oracle/admin/orcl/dpdump/artem.dmp
/u01/app/oracle/admin/orcl/dpdump/artem.dmp

6. execute import command with the export file into schema ivana
$ impdp ivana/ivana remap_schema=artem:ivana dumpfile=artem.dmp

Import: Release 12.1.0.2.0 - Production on Mon Jan 28 16:34:56 2019

Copyright © 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 and Real Application Testing options
Master table “IVANA”.“SYS_IMPORT_FULL_01” successfully loaded/unloaded
Starting “IVANA”.“SYS_IMPORT_FULL_01”: ivana/******** remap_schema=artem:ivana dumpfile=artem.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:“IVANA” already exists
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “IVANA”.“USERS” 7.929 KB 39 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “IVANA”.“SYS_IMPORT_FULL_01” completed with 1 error(s) at Mon Jan 28 16:35:42 2019 elapsed 0 00:00:43

7. check the target data
SQL> select object_name, object_type from dba_objects where owner=‘IVANA’;

OBJECT_NAME	OBJECT_TYPE
UI	INDEX
USERS	TABLE

SQL> select 1 from artem.users minus select 1 from ivana.users;

1

多租户环境中使用Data Pump
create PDB in NON-CDB

export data from NON-CDB, import into PDB