expdp之query、flashback_scn参数的使用

2025-04-08 技术教程

实验环境
操作系统：CentOS 7.1
数据库：Oracle 11.2.0.4

在使用10g后的Oracle Data Pump导出数据时, expdp中提供了query参数，可以在需要按条件导出表中部分数据时使用，它的使用就像是在select语句中的where条件使用一样。同时也提供了flashback_scn和flashback_time参数指定导出的时间点，这时oracle会使用flashback query查询导出scn时的数据，flashback query使用undo，无需打开flashback database功能。也就是说，只要undo信息不被覆盖，即使数据库被重启，仍然可以进行基于flashback_scn和flashback_time的导出动作。

--创建测试表

SYS@seiang11g>create tableseiang.test1 (id number,name varchar2(20)) tablespace seiang;

Table created.

SYS@seiang11g>create tableseiang.test2 (id number,time date) tablespace seiang;

Table created.

SYS@seiang11g>selectowner,table_name,tablespace_name from dba_tables where owner='SEIANG';

OWNER TABLE_NAME TABLESPACE_NAME

------------------------------------------------------------ ------------------------------

SEIANG TEST2 SEIANG

SEIANG TEST1 SEIANG

--插入测试数据

SYS@seiang11g>insert intoseiang.test1 select level,lpad(level,20,'*') from dual connect by level <=100;

100 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>insert intoseiang.test2 select level,sysdate-50+level from dual connect by level <=100;

100 rows created.

SYS@seiang11g>commit;

Commit complete.

--创建目录

SYS@seiang11g>create directorydumpdir as '/u01/app/oracle/exp_imp_dump';

Directory created.

--测试使用query导出，分别使用参数文件导出和单条语句导出

1、使用参数文件导出（query条件在parfile中不需要用'\'进行转义）

[oracle@seiang11g ~]$ vim/u01/app/oracle/exp_imp_dump/parfile_expdp.par

QUERY=seiang.test1:"whereid<=10"

NOLOGFILE=y

DIRECTORY=dumpdir

DUMPFILE=seiang.test1_parfile.dmp

tables=seiang.test1

[oracle@seiang11g ~]$ expdp seiang/seiangparfile=/u01/app/oracle/exp_imp_dump/parfile_expdp.par

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 15:39:09 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** parfile=/u01/app/oracle/exp_imp_dump/parfile_expdp.par

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 6.210 KB 30 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang.test1_parfile.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 15:39:14 2017 elapsed 0 00:00:05

2、单条语句进行导出

[oracle@seiang11g ~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1.dmp tables=seiang.test1query=seiang.test1:\"where id<=50\";

-bash:=50": No such file or directory

（特别注意：之所以出现上面的错误，是因为运算符也是需要做转义的。所以为了避免因为疏忽缺少转义而带来的错误，所有对于单条语句导出时，需要加一对单引号query=seiang.test1:'"where id <= 50"'）

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test1.dmp tables=seiang.test1 query=seiang.test1:'"whereid <= 50 "';

或者

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test11.dmp tables=seiang.test1 query='seiang.test1:"whereid <= 50 "';

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:37:48 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1.dmp tables=seiang.test1 query=seiang.test1:"where id<= 50 "

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 6.757 KB 50 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:38:01 2017 elapsed 0 00:00:08

--查询SCN号（第1个）

SYS@seiang11g>selectdbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1676032 (记录第一个SCN)

--再次插入30条测试数据

SYS@seiang11g>insert intoseiang.test1 select level,lpad(level,20,'#') from dual connect by level <=30;

30 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>insert intoseiang.test2 select level,sysdate-50+level from dual connect by level <= 30;

30 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>select count(*)from seiang.test1;

COUNT(*)

----------

130

SYS@seiang11g>select count(*)from seiang.test2;

COUNT(*)

----------

130

SYS@seiang11g>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296bytes

Fixed Size 2252824 bytes

Variable Size 822087656 bytes

Database Buffers 419430400 bytes

Redo Buffers 8892416 bytes

Database mounted.

Database opened.

--查看SCN号（第2个）

SYS@seiang11g>select current_scnfrom v$database;

CURRENT_SCN

-----------

1676913 (记录第二个SCN)

--再次插入10条测试数据

SYS@seiang11g>insert intoseiang.test1 select level,lpad(level,20,'@') from dual connect by level <=10;

10 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>insert intoseiang.test2 select level,sysdate-50+level from dual connect by level <= 10;

10 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>select count(*)from seiang.test1;

COUNT(*)

----------

140

SYS@seiang11g>select count(*)from seiang.test2;

COUNT(*)

----------

140

--查看SCN号（第3个）

SYS@seiang11g>select current_scnfrom v$database;

CURRENT_SCN

-----------

1677000 (记录第三个SCN)

--测试query和flashback_scn（第一个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1query=seiang.test1:\"where id\<=50\" flashback_scn=1676032

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:50:03 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1_1.dmp tables=seiang.test1 query=seiang.test1:"whereid<=50" flashback_scn=1676032

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 6.757 KB 50 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1_1.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:50:11 2017 elapsed 0 00:00:07

--测试query和flashback_scn（第2个SCN）导出

[oracle@seiang11g ~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1_3.dmp tables=seiang.test1query=seiang.test1:\"where id\<=50\" flashback_scn=1676913

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:51:58 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1_3.dmp tables=seiang.test1 query=seiang.test1:"whereid<=50" flashback_scn=1676913

Estimate in progress using BLOCKSmethod...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 7.578 KB 80 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1_3.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:52:05 2017 elapsed 0 00:00:06

--测试query和flashback_scn（第3个SCN）导出

[oracle@seiang11g ~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1_2.dmp tables=seiang.test1query=seiang.test1:\"where id\<=50\" flashback_scn=1677000

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:51:10 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1_2.dmp tables=seiang.test1 query=seiang.test1:"whereid<=50" flashback_scn=1677000

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 7.851 KB 90 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1_2.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:51:17 2017 elapsed 0 00:00:06

--测试复杂query导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdirdumpfile=seiang_test2_1.dmp tables=seiang.test2 query=seiang.test2:\"whereid in $ select id from seiang.test2 where time\<sysdate$\";

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:56:31 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test2_1.dmp tables=seiang.test2 query=seiang.test2:"whereid in ( select id from seiang.test2 where time<sysdate)"

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST2" 6.710 KB 90 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test2_1.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:56:38 2017 elapsed 0 00:00:06

--测试复杂query和flashback_scn（第1个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test2_2.dmp tables=seiang.test2 query=seiang.test2:\"whereid in $ select id from seiang.test2 where time\<sysdate$\"flashback_scn=1676032;

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:57:47 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test2_2.dmptables=seiang.test2 query=seiang.test2:"where id in ( select id fromseiang.test2 where time<sysdate)" flashback_scn=1676032

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST2" 6.125 KB 50 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test2_2.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:57:53 2017 elapsed 0 00:00:05

--测试复杂query和flashback_scn（第2个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test2_3.dmp tables=seiang.test2query=seiang.test2:\"where id in $ select id from seiang.test2 wheretime\<sysdate$\" flashback_scn=1676913;

Export: Release 11.2.0.4.0 - Productionon Wed Aug 16 14:58:23 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test2_3.dmp tables=seiang.test2 query=seiang.test2:"whereid in ( select id from seiang.test2 where time<sysdate)"flashback_scn=1676913

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST2" 6.562 KB 80 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test2_3.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:58:29 2017 elapsed 0 00:00:05

--测试复杂query和flashback_scn（第3个SCN）导出

[oracle@seiang11g ~]$ expdp seiang/seiangdirectory=dumpdir dumpfile=seiang_test2_4.dmp tables=seiang.test2query=seiang.test2:\"where id in $ select id from seiang.test2 wheretime\<sysdate$\" flashback_scn=1677000;

Export: Release 11.2.0.4.0 -Production on Wed Aug 16 14:58:57 2017

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, DataMining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test2_4.dmp tables=seiang.test2 query=seiang.test2:"whereid in ( select id from seiang.test2 where time<sysdate)"flashback_scn=1677000

Estimate in progress using BLOCKSmethod...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKSmethod: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST2" 6.710 KB 90 rows

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

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

Dump file set forSEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test2_4.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 14:59:03 2017 elapsed 0 00:00:05

多个表使用query条件则使用','分开

[oracle@seiang11g~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1AND2.dmptables=seiang.test1,seiang.test2 query=seiang.test1:\"whereid\<=50\",seiang.test2:'"where id<=50"';

Export:Release 11.2.0.4.0 - Production on Wed Aug 16 15:03:47 2017

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1AND2.dmp tables=seiang.test1,seiang.test2query=seiang.test1:"where id<=50",seiang.test2:"whereid<=50"

Estimatein progress using BLOCKS method...

Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA

Totalestimation using BLOCKS method: 128 KB

Processingobject type TABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 7.851 KB 90 rows

. . exported "SEIANG"."TEST2" 6.710 KB 90 rows

Mastertable "SEIANG"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded

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

Dumpfile set for SEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1AND2.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 15:03:54 2017 elapsed 0 00:00:05

--测试多表query和flashback_scn（第1个SCN）导出

[oracle@seiang11g~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_1.dmp tables=seiang.test1,seiang.test2query=seiang.test1:\"where id\<=50\",seiang.test2:'"whereid<=50"' flashback_scn=1676032;

Export:Release 11.2.0.4.0 - Production on Wed Aug 16 15:05:28 2017

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1AND2_1.dmp tables=seiang.test1,seiang.test2query=seiang.test1:"where id<=50",seiang.test2:"whereid<=50" flashback_scn=1676032

Estimatein progress using BLOCKS method...

Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA

Totalestimation using BLOCKS method: 128 KB

Processingobject type TABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 6.757 KB 50 rows

. . exported"SEIANG"."TEST2" 6.125 KB 50 rows

Mastertable "SEIANG"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded

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

Dumpfile set for SEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1AND2_1.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 15:05:34 2017 elapsed 0 00:00:05

--测试多表query和flashback_scn（第2个SCN）导出

[oracle@seiang11g~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_2.dmptables=seiang.test1,seiang.test2 query=seiang.test1:\"whereid\<=50\",seiang.test2:'"where id<=50"' flashback_scn=1676913;

Export:Release 11.2.0.4.0 - Production on Wed Aug 16 15:06:44 2017

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1AND2_2.dmp tables=seiang.test1,seiang.test2query=seiang.test1:"where id<=50",seiang.test2:"whereid<=50" flashback_scn=1676913

Estimatein progress using BLOCKS method...

Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA

Totalestimation using BLOCKS method: 128 KB

Processingobject type TABLE_EXPORT/TABLE/TABLE

. . exported"SEIANG"."TEST1" 7.578 KB 80 rows

. . exported"SEIANG"."TEST2" 6.562 KB 80 rows

Mastertable "SEIANG"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded

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

Dumpfile set for SEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1AND2_2.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 15:06:49 2017 elapsed 0 00:00:04

--测试多表query和flashback_scn（第3个SCN）导出

[oracle@seiang11g~]$ expdpseiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_3.dmptables=seiang.test1,seiang.test2 query=seiang.test1:\"whereid\<=50\",seiang.test2:'"where id<=50"' flashback_scn=1677000;

Export:Release 11.2.0.4.0 - Production on Wed Aug 16 15:07:21 2017

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

Starting"SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdirdumpfile=seiang_test1AND2_3.dmp tables=seiang.test1,seiang.test2query=seiang.test1:"where id<=50",seiang.test2:"whereid<=50" flashback_scn=1677000

Estimatein progress using BLOCKS method...

Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA

Totalestimation using BLOCKS method: 128 KB

Processingobject type TABLE_EXPORT/TABLE/TABLE

. . exported "SEIANG"."TEST1" 7.851 KB 90 rows

. . exported"SEIANG"."TEST2" 6.710 KB 90 rows

Mastertable "SEIANG"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded

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

Dumpfile set for SEIANG.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/exp_imp_dump/seiang_test1AND2_3.dmp

Job"SEIANG"."SYS_EXPORT_TABLE_01" successfully completed atWed Aug 16 15:07:25 2017 elapsed 0 00:00:04

以下是11g官方文档对flashback_scn、flashback_time、query参数的说明：

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

FLASHBACK_SCN

Default: Thereis no default

Purpose

Specifiesthe system change number (SCN) that Export will use toenable the Flashback Query utility.

指定导出将用于启用闪回查询实用程序的系统更改编号（SCN）。

Syntax and Description

FLASHBACK_SCN=scn_value

The exportoperation is performed with data that is consistent up to the specified SCN. IftheNETWORK_LINKparameter is specified, then the SCN refers to the SCN of the sourcedatabase.

Restrictions

FLASHBACK_SCNandFLASHBACK_TIMEare mutually exclusive.TheFLASHBACK_SCNparameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive.

限制

FLASHBACK_SCN和FLASHBACK_TIME是互斥的。

FLASHBACK_SCN参数仅适用于Oracle数据库的闪回查询功能。它不适用于闪回数据库，闪回删除或闪回数据存档。

Example

The followingexample assumes that an existing SCN value of384632exists. It exports thehrschema up to SCN 384632.

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmpFLASHBACK_SCN=384632

Note:

If you are on a logical standby systemand using a network link to access the logical standby primary, then theFLASHBACK_SCNparameter is ignored because SCNs areselected by logical standby.

FLASHBACK_TIME

Default: Thereis no default

Purpose

The SCNthat most closely matches the specified time is found,and this SCN is used to enable the Flashback utility. The export operation isperformed with data that is consistent up to this SCN.

找到与指定时间最匹配的SCN，此SCN用于启用闪回实用程序。导出操作使用与该SCN一致的数据执行。

Syntax and Description

FLASHBACK_TIME="TO_TIMESTAMP(time-value)"

BecausetheTO_TIMESTAMPvalue is enclosed in quotationmarks, it would be best to put this parameter in a parameter file.

因为TO_TIMESTAMP值用引号括起来，最好将此参数放在参数文件中。

Restrictions

FLASHBACK_TIMEandFLASHBACK_SCNare mutually exclusive.TheFLASHBACK_TIMEparameter pertains only to the flashback query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive.

限制

FLASHBACK_TIME和FLASHBACK_SCN是互斥的。

FLASHBACK_TIME参数仅适用于Oracle数据库的闪回查询功能。它不适用于闪回数据库，闪回或闪回数据存档。

Example

You can specifythe time in any format that theDBMS_FLASHBACK.ENABLE_AT_TIMEprocedure accepts. For example,suppose you have a parameter file,flashback.par, with the following contents:

DIRECTORY=dpump_dir1

DUMPFILE=hr_time.dmp

FLASHBACK_TIME="TO_TIMESTAMP('25-08-200814:35:00', 'DD-MM-YYYY HH24:MI:SS')"

You could thenissue the following command:

> expdp hrPARFILE=flashback.par

The exportoperation will be performed with data that is consistent with the SCN that mostclosely matches the specified time.

Note:

If you are on a logical standby systemand using a network link to access the logical standby primary, then theFLASHBACK_SCNparameter is ignored because SCNs areselected by logical standby. SeeOracle Data Guard Concepts andAdministrationfor information about logical standby databases.

QUERY

Default: There is nodefault

Purpose

Allows you to specify aquery clause that is used to filter the data that gets exported.

Syntaxand Description

QUERY = [schema.][table_name:] query_clause

Thequery_clauseis typically a SQLWHEREclause for fine-grained row selection, but could be any SQLclause. For example, anORDERBYclause could be used to speed up a migration from aheap-organized table to an index-organized table. If a schema and table nameare not supplied, then the query is applied to (and must be valid for) alltables in the export job. A table-specific query overrides a query applied toall tables.

query_clause通常是用于细粒度行选择的SQL WHERE子句，但也可以是任何SQL子句。例如，ORDER BY子句可用于加速从堆组织表到索引组织表的迁移。如果未提供schema和表名称，则该查询将应用于导出作业中的所有表（并且必须有效）。表特定的查询覆盖了应用于所有表的查询。

When the query is to beapplied to a specific table, a colon must separate the table name from thequery clause. More than one table-specific query can be specified, but only onequery can be specified per table.

当查询应用于特定表时，冒号必须将表名与查询子句分开。可以指定多个表特定的查询，但是每个表只能指定一个查询。

If theNETWORK_LINKparameter is specified along with theQUERYparameter, then any objects specified in thequery_clausethat are on the remote (source) node must be explicitlyqualified with theNETWORK_LINKvalue. Otherwise, Data Pump assumes that the object is on thelocal (target) node; if it is not, then an error is returned and the import ofthe table from the remote (source) system fails.

如果NETWORK_LINK参数与QUERY参数一起指定，则在远程（源）节点上的query_clause中指定的任何对象都必须使用NETWORK_LINK值明确限定。否则，数据泵假定对象在本地（目标）节点上;如果不是，则返回错误，并且从远程（源）系统导入表失败。

For example, if youspecifyNETWORK_LINK=dblink1, then thequery_clauseof theQUERYparameter must specify that link, as shown in the followingexample:

QUERY=(hr.employees:"WHERE last_name IN(SELECT last_name

FROM hr.employees@dblink1)")

Depending on youroperating system, the use of quotation marks when you specify a value for thisparameter may also require that you use escape characters. Oracle recommendsthat you place this parameter in a parameter file, which can reduce the numberof escape characters that might otherwise be needed on the command line. See"Use of QuotationMarks On the Data Pump Command Line".

To specify a schema otherthan your own in a table-specific query, you must be granted access to thatspecific table.

Restrictions

·TheQUERYparameter cannot be used with the following parameters:

oCONTENT=METADATA_ONLY

oESTIMATE_ONLY

oTRANSPORT_TABLESPACES

·When theQUERYparameter is specified for a table, Data Pump uses externaltables to unload the target table. External tables uses a SQLCREATE TABLE ASSELECTstatement. Thevalue of theQUERYparameter is theWHEREclause in theSELECTportion of theCREATETABLEstatement. IftheQUERYparameter includes references to another table with columnswhose names match the table being unloaded, and if those columns are used inthe query, then you will need to use a table alias to distinguish betweencolumns in the table being unloaded and columns in theSELECTstatement with the same name. The table alias used by Data Pumpfor the table being unloaded isKU$.

QUERY参数不能与以下参数一起使用：

CONTENT =METADATA_ONLY

ESTIMATE_ONLY

TRANSPORT_TABLESPACES

当为表指定QUERY参数时，Data Pump使用外部表来卸载目标表。外部表使用SQL CREATE TABLE AS SELECT语句。 QUERY参数的值是CREATE TABLE语句的SELECT部分中的WHERE子句。如果QUERY参数包含对其名称与卸载表匹配的列的另一个表的引用，并且如果在查询中使用这些列，则需要使用表别名来区分要卸载的表中的列和具有相同名称的SELECT语句。 Data Pump为卸载的表使用的表别名为KU $。

For example,suppose you want to export a subset of thesh.salestable based on the credit limit for a customer in thesh.customerstable. In the following example,KU$is used to qualify thecust_idfield in theQUERYparameter for unloadingsh.sales. As a result,Data Pump exports only rows for customers whose credit limit is greater than$10,000.

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c

WHERE cust_credit_limit > 10000 AND ku$.cust_id = c.cust_id)"'

If, as in thefollowing query,KU$is not used for a table alias, then the result will be that allrows are unloaded:

QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c

WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)"'

·The maximum length allowed for aQUERYstring is 4000 bytes including quotation marks, which means thatthe actual maximum length allowed is 3998 bytes.

Example

The following is anexample of using theQUERYparameter:

> expdp hr PARFILE=emp_query.par

The contents of theemp_query.par file are as follows:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

NOLOGFILE=YES

DIRECTORY=dpump_dir1

DUMPFILE=exp1.dmp

This example unloads alltables in thehrschema, but only the rows that fit the query expression. In thiscase, all rows in all tables (exceptemployees) in thehrschema will beunloaded. For theemployeestable, only rows that meet the query criteria are unloaded.

Oracle使用数据泵 (expdp/impdp)实施迁移

作者：SEian.G（苦练七十二变，笑对八十一难）

声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。