ORACLE DATAPUMP的学习

1.创建目录
create directory data1 as '/home/oracle/data1';

Directory created.

2.把目录权限给某个用户。
 grant read, write on directory data1 to hr;

Grant succeeded.

3.导出一个用户的数据到这个目录下。
expdp hr/hr schemas=hr dumpfile=exphr1.dmp directory=data1 parallel=2 job_name=hr1

4.只导出用户下面的表的元数据，如只有表结构，索引等信息。
expdp hr/hr schemas=hr dumpfile=exphr2.dmp content=metadata_only directory=data1 parallel=2 job_name=hr1

5. 只导出用户下面的表的数据。
 expdp hr/hr schemas=hr dumpfile=exphr3.dmp content=data_only directory=data1 parallel=2 job_name=hr1

6.导出用户下面的所有对象以及数据
expdp hr/hr schemas=hr dumpfile=exphr3.dmp content=all directory=data1 parallel=2 job_name=hr1

在这里说下content这个参数的意思：为ALL时，将导出对象定义及其所有数据。 为DATA_ONLY时，只导出对象数据。METADATA_ONLY时，只导出对象定义。

7.ESTIMATE： 指定估算被导出表所占用磁盘空间方法，默认值班为BLOCKS ESTIMATE=BLOCKS | STATISTICS
设置为BLOCKS时，ORACLE 会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间，设置为STATISTICS时，根据最近统计值估算对象占用空间。
expdp hr/hr schemas=hr dumpfile=exphr3.dmp content=all estimate=blocks | statistics directory=data1 parallel=2 job_name=hr1

8.ESTIMATE_ONLY : 指定是否只估算导出作业所占用的磁盘空间，默认值为N。
ESTIMATE_ONLY=Y|N
设置为Y时，导出作用只估算对象所占用的磁盘空间，而不会执行导出作业，为N时，不公估算对像所占用的磁盘空间，还会执行导出操作。
expdp hr/hr schemas=hr  content=all estimate=blocks  estimate_only=y|n  parallel=2 job_name=hr1

9. exclude:该项用于指定执行操作时释放要排除对象类型或相关对象。EXCLUDE=OBJECT_TYPE [:name_clause] object_name用于指定要排除的对象类型，name_clause用于指定要排除的具体对象，EXCLUDE 和INCLUDE不能同时使用。

10. filesize 指定导出文件的最大尺寸，默认为0（表示没有尺寸限制）

11.flashback_scn
指定导出特定SCN时刻的表数据
SCN_VALUE用于标识SCN值， FLASHBACK_SCN 和FLASHBACK_TIME不能同时使用
测试：建立新表：
SQL> create table test1(id number(9));

Table created.

SQL> insert into test1 values(8);

1 row created.

SQL> insert into test1 values(9);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012-04-10 12:46:42

SQL> delete from test1;

2 rows deleted.

SQL> commit;

Commit complete.
现已删除了数据，执行一个转换，把时间换为SCN。
SQL> select timestamp_to_scn(to_timestamp('2012-04-10 12:46:42','yyyy-mm-dd hh24:mi:ss')) from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2012-04-1012:46:42','YYYY-MM-DDHH24:MI:SS'))
---------------------------------------------------------------------------
                                                                     881465
执行导出：
 expdp hr/hr tables=test1 dumpfile=hrtest1.dmp directory=data1 flashback_scn=881465  parallel=2

Export: Release 11.2.0.1.0 - Production on Tue Apr 10 12:48:16 2012

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** tables=test1 dumpfile=hrtest1.dmp directory=data1 flashback_scn=881465 parallel=2
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 "HR"."TEST1"                                5.054 KB       2 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /home/oracle/data1/hrtest1.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:48:20

在这里可以清楚的看到那两行数据被导出来了。

12。 FLASHBACK_TIME： 指定导出特定时间点的表数据
 expdp hr/hr tables=test1 dumpfile=hrtest2.dmp directory=data1 flashback_time=\"to_timestamp\(\'2012-04-10 12:46:42\'\,\'yyyy-mm-dd hh24:mi:ss\'\)\"  parallel=2

Export: Release 11.2.0.1.0 - Production on Tue Apr 10 12:49:28 2012

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** tables=test1 dumpfile=hrtest2.dmp directory=data1 flashback_time="to_timestamp('2012-04-10 12:46:42','yyyy-mm-dd hh24:mi:ss')" parallel=2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "HR"."TEST1"                                5.054 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /home/oracle/data1/hrtest2.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:49:31

13.NETWORK_LINK：指定数据库链名，如果要将远程数据库对象导出到本地例程的转储文件中，必须设置该选项。
DBLINK建立：
第一种方法：
（1） 建立TNS服务名：
dblink =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl)
    )
  )
（2） 测试TNS服务名
tnsping dblink

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 10-APR-2012 13:02:25

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (INSTANCE_NAME = orcl)))
OK (0 msec)
（3）创建DBLINK
create database link dbtest connect to hr identified by hr using'dblink';

Database link created.

第二种方法：
SQL> create database link dbtest1 connect to hr identified by hr using'(DESCRIPTION=
  2  (ADDRESS_LIST =
  3  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT=1521))
  4  )
  5  (CONNECT_DATA =
  6  (SERVICE_NAME = orcl)
  7  )
  8  )';

Database link created.

使用DBLINK导出：
expdp hr/hr schemas=hr network_link=dbtest dumpfile=hr2.dmp directory=data1 parallel=2

Export: Release 11.2.0.1.0 - Production on Tue Apr 10 13:03:48 2012

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/******** schemas=hr network_link=dbtest dumpfile=hr2.dmp directory=data1 parallel=2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 704 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/DB_LINK
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
. . exported "HR"."TEST1"                                    0 KB       0 rows
. . exported "HR"."SYS_EXPORT_SCHEMA_01"                 241.6 KB    1098 rows
. . exported "HR"."COUNTRIES"                            6.273 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.81 KB     107 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
. . exported "HR"."TEST23"                               16.80 KB     107 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/data1/hr2.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:04:04

14.PARALLEL： 指定执行导出操作的并行进程个数，默认值为1.

15。QUERY： 用于指定过滤导出数据的WHERE 条件。
expdp hr/hr tables=employees dumpfile=employees.dmp directory=data1 parallel=2 query=\"where employee_id \= 100\"

16.SCHEMAS： 该方案用地指定执行方案模式导出，默认为当前用户方案。

17。STATUS：指定显示导出作用进程的详细的详细状态默认值为0。

18。TABLES： 可以指定表导出。

19.TABLESPACES： 可以指定表空间导出。

20。TRANSPORT_FULL_CHECK： 该选项用于指定被搬移表空间和未搬移表空间关系的检查方式，默认为N。
当设置为Y时，导出作用会检查表空间直接的完整关联关系，如果表空间所在表空间或其索引所在的表空间只有一个表空间被搬移，将显示错误信息。当设置为N时，导出作用只检查单端依赖，如果搬移索引所在表空间，未搬移索引表空间则不会显示错误信息。

21.TRANSPORT_TABLESPACES： 指定执行表空间模式导出

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22740983/viewspace-734631/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22740983/viewspace-734631/