grant read,write on directory directory_name to 执行导出命令的用户;

 expdp scott/tiger directory=dump_dir dumpfile=dept_emp.dmp  logfile=exp_dept_emp.log tables=dept,emp

 

导出某个对象的表结构(content控制导出的结构、数据还是全部。但是这种方法控制的是导出对象),现在需要对其中的个别对象导出表结构。这时可以通过query来控制:

[oracle@localhost /]$ expdp scott/tiger directory=dump_dir dumpfile=dept_emp2.dmp  logfile=exp_dept_emp2.log tables=dept,emp query='dept:"where 1=2"'

 

Export: Release 11.2.0.1.0 – Production on Mon Jun 6 23:37:33 2016

 

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=dept_emp2.dmp logfile=exp_dept_emp2.log tables=dept,emp query=dept:"where 1=2"

Estimate in progress using BLOCKS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.843 KB       0 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oracle/dept_emp2.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 23:37:46

对表emp添加一个恒为false的查询条件,是的emp表导出的时候获取零条记录,从而达到只导emp的结构的目的。

不过这种方法对于数据量比较大的表,效率会比较低。

而数据泵还有更好的方法来解决这个问题:使用exclude参数。

[oracle@localhost /]$ expdp scott/tiger directory=dump_dir dumpfile=dept_emp5.dmp logfile=exp_dept_emp5.log tables=dept,emp exclude=table/table_data:\"=\'EMP\'\"

 

Export: Release 11.2.0.1.0 – Production on Tue Jun 7 00:09:04 2016

 

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=dept_emp5.dmp logfile=exp_dept_emp5.log tables=dept,emp exclude=table/table_data:"='EMP'"

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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oracle/dept_emp5.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 00:09:19

虽然在导出日志中看不到emp表的信息,但是可以通过下面的测试检查导出是否生效

[oracle@localhost /]$ impdp scott/tiger directory=dump_dir dumpfile=dept_emp5.dmp tables=emp

 

Import: Release 11.2.0.1.0 – Production on Tue Jun 7 00:41:43 2016

 

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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=dept_emp5.dmp tables=emp

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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 00:41:50

 

[oracle@localhost /]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 7 00:42:21 2016

 

Copyright (c) 1982, 2009, Oracle.  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

 

SQL> conn scott/tiger

Connected.

SQL> desc emp

 Name                                      Null?    Type

 —————————————– ——– —————————-

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

很显然,利用exclude的方式使得数据泵导出的时候去掉了emp表的数据。

这个方法也适用于分区表,下面将emp表变为分区表

SQL> drop table emp purge;

 

Table dropped.

 

SQL> create table emp

  2  (id number,

  3  name varchar2(30))

  4  partition by range (id)

  5  (partition p1 values less than (10000),

  6  partition p2 values less than (20000),

  7  partition p3 values less than (maxvalue));

 

Table created.

SQL> insert into emp

  2  select rownum,object_name from all_objects;

 

55640 rows created.

 

SQL> commit;

 

Commit complete.

 

不通之处在于导出exclude的table_data语法总不需要执行表名emp,而需要指定分区的名称:

[oracle@localhost ~]$ expdp scott/tiger directory=dump_dir dumpfile=emp_dept.dmp tables=emp,dept exclude=table_data:\"in\(\'P1\',\'P2\',\'P3\'\)\"

 

Export: Release 11.2.0.1.0 – Production on Mon Jun 13 16:30:40 2016

 

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=emp_dept.dmp tables=emp,dept exclude=table_data:"in('P1','P2','P3')"

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/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oracle/emp_dept.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:31:05

由于数据泵支持各种sql语法,对于当前的情况,还可以用下面的导出方式进行简化

[oracle@localhost ~]$ expdp scott/tiger directory=dump_dir dumpfile=scott10.dmp tables=emp,dept exclude=table_data:\"like \'P%\'\"

 

Export: Release 11.2.0.1.0 – Production on Tue Jun 21 17:56:58 2016

 

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

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=scott10.dmp tables=emp,dept exclude=table_data:"like 'P%'"

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/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oracle/scott10.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:57:19

对已经存在的表加载索引

有盆友使用数据泵导入时,到表存在时,如何在不删除表的情况下,导入表的数据和索引

       这个任务对于imp很简单,因为imp的共总方式就是如此。

       而impdp的默认工作方式并非如此,如果监测到表存在,impdp会跳过索引的创建

为了同时导入数据和索引,通过include=indexinclude=table_data配合,就可以解决这个问题了

 

 

 

 

 

 




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件