ORACLE数据库中impd expd的几个使用范例

弹唱中国,终极寸芒,如何缩阴

#按照表导出

[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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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"."family1":  u1/******** tables=family dumpfile=u1_family.dump directory=dump_dir job_name=family1

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

. . exported "u1"."family"                               5.984 kb       2 rows

master table "u1"."family1" successfully loaded/unloaded

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

dump file set for u1.family1 is:

  /oracle/product/10.2.0.5/rdbms/log/u1_family.dump

job "u1"."family1" successfully completed at 12:58:44

#跨用户导入:

[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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

copyright (c) 2003, 2007, oracle.  all rights reserved.

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

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复