Oracle database 14章 使用数据泵导出和导入 导入 理论试验

1、导入命令--生成导入DDL 文件

~/app/oracle/admin/test/dpdump> impdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp SQLFILE=data_pump_dir:hr.sql SCHEMAS=HRImport: Release 11.2.0.1.0 - Production on Thu Apr 4 15:58:28 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloadedStarting "HR"."SYS_SQL_FILE_SCHEMA_01":  hr/******** directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp SQLFILE=data_pump_dir:hr.sql SCHEMAS=HR Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 15:58:30oracle@linux-tpch:~/app/oracle/admin/test/dpdump> 

需要注意的是，加了

SQLFILE=data_pump_dir:hr.sql

这个参数后，会在目录对象下生成 hr.sql这个文件，但是，上面的这个操作也仅仅是生成这个文件而已，这个文件是DDL文件，即导入的DDL操作过程，并没有实际的进行导入。

只有将这个参数去掉后才是真正的导入。

1、导入命令---实际导入

先在HR schemas  中新创建一张表 test_countries,之后导出，然后将该表删除：

create table test_countries as select * from countries;

~/app/oracle/admin/test/dpdump> expdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp REUSE_DUMPFILES=Y

drop table  test_countries  purge；

然后再导入，可以看到  test_countries这张表又回来了。

~/app/oracle/admin/test/dpdump> impdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp  SCHEMAS=HRImport: Release 11.2.0.1.0 - Production on Thu Apr 4 15:56:09 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "HR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "HR"."SYS_IMPORT_SCHEMA_01":  hr/******** directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp SCHEMAS=HR Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECORA-31684: Object type TYPE:"HR"."YEARLY_SPECIAL_SERVICE_ROW" already existsProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEORA-31684: Object type SEQUENCE:"HR"."LOCATIONS_SEQ" already existsORA-31684: Object type SEQUENCE:"HR"."DEPARTMENTS_SEQ" already existsORA-31684: Object type SEQUENCE:"HR"."EMPLOYEES_SEQ" already existsProcessing object type SCHEMA_EXPORT/TABLE/TABLEORA-39151: Table "HR"."COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."REGIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."LOCATIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."JOBS" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."JOB_HISTORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_COUNTRIES1" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_COUNTRIES2" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_COUNTRIES3" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES1" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES2" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES3" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."EMPLOYEES_EXT" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES5" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."NEW_EMPLOYEES6" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."SERVICE_DATA" exists. All dependent metadata and data will be skipped due to table_exists_action of skipORA-39151: Table "HR"."YEARLY_SPECIAL_SERVICE_COST" exists. All dependent metadata and data will be skipped due to table_exists_action of skipProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "HR"."TEST_COUNTRIES"                       6.289 KB      25 rowsProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already existsORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already existsProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already existsProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "HR"."SYS_IMPORT_SCHEMA_01" completed with 28 error(s) at 15:56:10

查看 test_countries 这张表：

SQL> select * from test_COUNTRIES;CO COUNTRY_NAME      REGION_ID-- ---------------------------------------- ----------AR Argentina     2AU Australia     3BE Belgium     1BR Brazil     2CA Canada     2CH Switzerland     1CN China     3DE Germany     1DK Denmark     1EG Egypt     4FR France     1CO COUNTRY_NAME      REGION_ID-- ---------------------------------------- ----------