expdp之query、flashback_scn参数的使用

发布时间：2020-08-16 21:51:43 阅读：369 作者：迷倪小魏栏目：关系型数据库

开发者测试专用服务器限时活动，0元免费领，库存有限，领完即止！点击查看>>

实验环境
操作系统：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 table seiang.test1 (id number,name varchar2(20)) tablespace seiang;

Table created.

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

Table created.

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

OWNER TABLE_NAME TABLESPACE_NAME

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

SEIANG TEST2 SEIANG

SEIANG TEST1 SEIANG

--插入测试数据

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

100 rows created.

SYS@seiang11g>commit;

Commit complete.

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

100 rows created.

SYS@seiang11g>commit;

Commit complete.

--创建目录

SYS@seiang11g>create directory dumpdir 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:"where id<=10"

NOLOGFILE=y

DIRECTORY=dumpdir

DUMPFILE=seiang.test1_parfile.dmp

tables=seiang.test1

[oracle@seiang11g ~]$ expdp seiang/seiang parfile=/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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining 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 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 "SEIANG"."TEST1" 6.210 KB 30 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

2、单条语句进行导出

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

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

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

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

或者

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

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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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 "SEIANG"."TEST1" 6.757 KB 50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

--查询SCN号（第1个）

SYS@seiang11g>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

1676032 (记录第一个SCN)

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

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

30 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>insert into seiang.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 1252663296 bytes

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_scn from v$database;

CURRENT_SCN

-----------

1676913 (记录第二个SCN)

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

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

10 rows created.

SYS@seiang11g>commit;

Commit complete.

SYS@seiang11g>insert into seiang.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_scn from v$database;

CURRENT_SCN

-----------

1677000 (记录第三个SCN)

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1 query=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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1_1.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1676032

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 "SEIANG"."TEST1" 6.757 KB 50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_3.dmp tables=seiang.test1 query=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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1_3.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1676913

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 "SEIANG"."TEST1" 7.578 KB 80 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1_2.dmp tables=seiang.test1 query=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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1_2.dmp tables=seiang.test1 query=seiang.test1:"where id<=50" flashback_scn=1677000

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 "SEIANG"."TEST1" 7.851 KB 90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

--测试复杂query导出

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_1.dmp tables=seiang.test2 query=seiang.test2:\"where id 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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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 "SEIANG"."TEST2" 6.710 KB 90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test2_2.dmp tables=seiang.test2 query=seiang.test2:\"where id 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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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 "SEIANG"."TEST2" 6.125 KB 50 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

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

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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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 "SEIANG"."TEST2" 6.562 KB 80 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

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

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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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 "SEIANG"."TEST2" 6.710 KB 90 rows

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

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

Dump file set for SEIANG.SYS_EXPORT_TABLE_01 is:

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

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

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

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

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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1AND2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50"

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

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

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

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

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

Dump file 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 at Wed Aug 16 15:03:54 2017 elapsed 0 00:00:05

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

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

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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_1.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1676032

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

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

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

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

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

Dump file 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 at Wed Aug 16 15:05:34 2017 elapsed 0 00:00:05

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_2.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1676913

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

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

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

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

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

Dump file 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 at Wed Aug 16 15:06:49 2017 elapsed 0 00:00:04

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

[oracle@seiang11g ~]$ expdp seiang/seiang directory=dumpdir dumpfile=seiang_test1AND2_3.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:\"where id\<=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

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SEIANG"."SYS_EXPORT_TABLE_01": seiang/******** directory=dumpdir dumpfile=seiang_test1AND2_3.dmp tables=seiang.test1,seiang.test2 query=seiang.test1:"where id<=50",seiang.test2:"where id<=50" flashback_scn=1677000

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

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

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

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

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

Dump file 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 at Wed Aug 16 15:07:25 2017 elapsed 0 00:00:04

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

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

FLASHBACK_SCN

Default: There is no default

Purpose

Specifies the system change number (SCN) that Export will use to enable the Flashback Query utility.

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

Syntax and Description

FLASHBACK_SCN=scn_value

The export operation is performed with data that is consistent up to the specified SCN. If the NETWORK_LINK parameter is specified, then the SCN refers to the SCN of the source database.

Restrictions

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.
The FLASHBACK_SCN parameter 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 following example assumes that an existing SCN value of 384632 exists. It exports the hr schema up to SCN 384632.

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632

Note:

If you are on a logical standby system and using a network link to access the logical standby primary, then the FLASHBACK_SCNparameter is ignored because SCNs are selected by logical standby.

FLASHBACK_TIME

Default: There is no default

Purpose

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

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

Syntax and Description

FLASHBACK_TIME="TO_TIMESTAMP(time-value)"

Because the TO_TIMESTAMP value is enclosed in quotation marks, it would be best to put this parameter in a parameter file.

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

Restrictions

FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.
The FLASHBACK_TIME parameter 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 specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_TIME procedure 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-2008 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

You could then issue the following command:

> expdp hr PARFILE=flashback.par

The export operation will be performed with data that is consistent with the SCN that most closely matches the specified time.

Note:

If you are on a logical standby system and using a network link to access the logical standby primary, then the FLASHBACK_SCNparameter is ignored because SCNs are selected by logical standby. See Oracle Data Guard Concepts and Administration for information about logical standby databases.

QUERY

Default: There is no default

Purpose

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

Syntax and Description

QUERY = [schema.][table_name:] query_clause

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

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

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

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

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

FROM hr.employees@dblink1)")

To specify a schema other than your own in a table-specific query, you must be granted access to that specific table.

Restrictions

o CONTENT=METADATA_ONLY

o ESTIMATE_ONLY

o TRANSPORT_TABLESPACES

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 $。

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

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

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

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

Example

> expdp hr PARFILE=emp_query.par

The contents of the emp_query.par file are as follows:

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

NOLOGFILE=YES

DIRECTORY=dpump_dir1

DUMPFILE=exp1.dmp

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>

向AI问一下细节

expdp之query、flashback_scn参数的使用

QUERY

猜你喜欢

最新资讯

相关推荐

开发者交流群：

相关标签