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

最新推荐文章于 2025-08-02 11:19:27 发布

原创最新推荐文章于 2025-08-02 11:19:27 发布 · 948 阅读

0 ·

CC 4.0 BY-SA版权

Oracle database实践专栏收录该内容

13 篇文章

订阅专栏

本文介绍了如何使用Oracle数据泵进行数据的导出和导入操作。通过一个实例展示了如何生成导入DDL文件，以及如何实际执行导入，使得在删除表后能够重新恢复数据。

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=HR

Import: Release 11.2.0.1.0 - Production on Thu Apr 4 15:58:28 2013

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "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_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 15:58:30

oracle@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=HR

Import: Release 11.2.0.1.0 - Production on Thu Apr 4 15:56:09 2013

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "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_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"HR"."YEARLY_SPECIAL_SERVICE_ROW" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"HR"."LOCATIONS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR"."DEPARTMENTS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR"."EMPLOYEES_SEQ" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."REGIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."LOCATIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."JOBS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."JOB_HISTORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_COUNTRIES1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_COUNTRIES2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_COUNTRIES3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."EMPLOYEES_EXT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES5" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."NEW_EMPLOYEES6" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."SERVICE_DATA" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."YEARLY_SPECIAL_SERVICE_COST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST_COUNTRIES"                       6.289 KB      25 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "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					     2
AU Australia					     3
BE Belgium					     1
BR Brazil					     2
CA Canada					     2
CH Switzerland					     1
CN China					     3
DE Germany					     1
DK Denmark					     1
EG Egypt					     4
FR France					     1

CO COUNTRY_NAME 			     REGION_ID
-- ---------------------------------------- ----------