【ORACLE】impd expd的几个例子

来源：互联网发布：搜狗输入法云计算候选编辑：程序博客网时间：2024/06/06 05:21

**********************************************

**原文： blog.csdn.net/clark_xu 徐长亮专栏

**********************************************

#按照表导出
[oracle@host2 ~]$ expdp u1/tiger tables=family dumpfile='u1_family.dump' directory=dump_dir job_name=family1

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 12:58:39

#跨用户导入:
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1_family_1.dump directory=dump_dir REMAP_SCHEMA=u1:u2

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:23:26

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "U2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U2"."SYS_IMPORT_FULL_01": u2/******** dumpfile=u1_family_1.dump directory=dump_dir REMAP_SCHEMA=u1:u2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."FAMILY" 5.984 KB 2 rows
Job "U2"."SYS_IMPORT_FULL_01" successfully completed at 13:23:30
#修改路径
create or replace directory dump_dir as '/oracle/backup';
grant read,write on directory dump_dir to u2;
#按照用户导出
[oracle@host2 ~]$ expdp u1/tiger schemas=u1 dumpfile='u1.dump' directory=dump_dir

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:25:40

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** schemas=u1 dumpfile=u1.dump directory=dump_dir
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/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
. . exported "U1"."FAMILY" 5.984 KB 2 rows
Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_01 is:
/oracle/backup/u1.dump
Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:26:06
#导入按照用户导出的数据
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1.dump directory=dump_dir REMAP_SCHEMA=u1:u2

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:26:58

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "U2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U2"."SYS_IMPORT_FULL_01": u2/******** dumpfile=u1.dump directory=dump_dir REMAP_SCHEMA=u1:u2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"U2" 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 "U2"."FAMILY" 5.984 KB 2 rows
Job "U2"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:27:02

#按照表空间导出
[oracle@host2 ~]$ expdp u1/tiger tablespace=u1 dumpfile='u1_u1.dump' directory=dump_dir
LRM-00101: unknown parameter name 'tablespace'

[oracle@host2 ~]$ expdp u1/tiger tablespaces=u1 dumpfile='u1_u1.dump' directory=dump_dir

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:28:39

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "U1"."SYS_EXPORT_TABLESPACE_01": u1/******** tablespaces=u1 dumpfile=u1_u1.dump directory=dump_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "U1"."FAMILY" 5.984 KB 2 rows
Master table "U1"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_TABLESPACE_01 is:
/oracle/backup/u1_u1.dump
Job "U1"."SYS_EXPORT_TABLESPACE_01" successfully completed at 13:29:34

#通过表空间导入（remap_tablespace remap_user）
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:39:35

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "U2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U2"."SYS_IMPORT_FULL_01": u2/******** dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "U3"."FAMILY" 5.984 KB 2 rows
Job "U2"."SYS_IMPORT_FULL_01" successfully completed at 13:39:37
#导入导出，要新建相应的表空间和用户shemas

#表的追加
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3 table_exists_action=append
#表的替换
[oracle@host2 ~]$ impdp u2/tiger dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3 table_exists_action=replace;

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 11 August, 2014 13:44:18

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "U2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "U2"."SYS_IMPORT_FULL_01": u2/******** dumpfile=u1_u1.dump directory=dump_dir REMAP_TABLESPACE=u1:u3 REMAP_SCHEMA=u1:u3 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "U3"."FAMILY" 5.984 KB 2 rows
Job "U2"."SYS_IMPORT_FULL_01" successfully completed at 13:44:21

0 0