oracle导入和导出工具impdp/expdp

最新推荐文章于 2024-04-16 15:04:27 发布

原创最新推荐文章于 2024-04-16 15:04:27 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #工具 #schema #object #table #application

oracle10g 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了如何使用Oracle数据泵工具expdp和impdp进行数据导出和导入操作，包括创建导出环境、导出数据、删除表、导入数据以及验证导入成功的全过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle的导入/导出技术，推荐使用expdp/impdp来代替之前的exp/imp。
expdp数据泵导出：
1. 创建导出实验环境
[oracle@station10 ~]$ mkdir -p /u01/oracle/backup/
[oracle@station10 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 16:23:14 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL> create directory backupdir as '/u01/oracle/backup';
Directory created.
SQL> create user test identified by test;
User created.
SQL> create tablespace test datafile '/u01/oracle/oradata/ORCL/test01.dbf' size 100M;
Tablespace created.
SQL> alter user test default tablespace test;
User altered.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> grant select on dba_objects to test;
Grant succeeded.
SQL> grant select on dba_tables to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table v_objects as select * from dba_objects;
Table created.
SQL> create table v_tables as select * from dba_tables;
Table created.
SQL> select count(*) from v_objects;
  COUNT(*)
----------
   50711
SQL> select count(*) from v_tables;
  COUNT(*)
----------
   1597

2.expdp数据泵导出刚刚创建的数据
[oracle@station10 backup]$ expdp system/sys dumpfile=testdump logfile=testdump.log schemas=TEST directory=backupdir
Export: Release 10.2.0.4.0 - Production on Tuesday, 20 March, 2012 16:28:15
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=testdump logfile=testdump.log schemas=TEST directory=backupdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.437 MB
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
. . exported "TEST"."V_OBJECTS"                         4.732 MB 50711 rows
. . exported "TEST"."V_TABLES"                         349.2 KB 1597 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/oracle/backup/testdump.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:29:03
[oracle@station10 backup]$ ll
总计 5412
-rw-r----- 1 oracle oinstall 5517312 03-20 16:29 testdump.dmp
-rw-r--r-- 1 oracle oinstall 1351 03-20 16:29 testdump.log

3. 删除之前创建的表，以便使用impdp来测试导入
SQL> conn test/test
Connected.
SQL> drop table v_objects;
Table dropped.
SQL> drop table v_tables;
Table dropped.
SQL> select count(*) from v_objects;
select count(*) from v_objects
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from v_tables;
select count(*) from v_tables
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

4. 使用impdp工具导入，恢复刚刚删除的测试表
[oracle@station10 backup]$ impdp system/sys dumpfile=testdump logfile=dumptest.log schemas=TEST directory=backupdir
Import: Release 10.2.0.4.0 - Production on Tuesday, 20 March, 2012 16:37:00
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=testdump logfile=dumptest.log schemas=TEST directory=backupdir
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" 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 "TEST"."V_OBJECTS"                         4.732 MB 50711 rows
. . imported "TEST"."V_TABLES"                         349.2 KB 1597 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 16:37:08

5. 验证impdp导入是否成功
SQL> conn test/test
Connected.
SQL> select count(*) from v_objects;
  COUNT(*)
----------
   50711
SQL> select count(*) from v_tables;
  COUNT(*)
----------
   1597

结论：通过查询结果验证，导入成功。

本文只是简单演示了利用Oracle的expdp/impdp来实现数据导出/导入功能，现只能起到抛砖引玉的作用；其强大的功能及应用，还需要各位自己去实践及验证；多谢!