Oracle expdp导出分区表，query条件带有rownum
前言
在做数据脱敏的时候，对一张刚好是分区表的表做导出，为了只取出部分数据看是否数据可以正常脱敏，在query中带上rownum。
结果发现是每个分区都取出了rownum的限定行数。
比如：rownum<=5，正常去查询表的话是只会有5行的结果，
但是expdp导出分区表，带rownum<=5，则是每个分区都取出符合条件的5行。
这应该算BUG吧？
环境模拟
构造分区表
create table scott.t_partition_range (id number)
partition by range(id)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition pmax values less than (maxvalue)
);
模板复制
SYS@zkm> create table scott.t_partition_range (id number)
2 partition by range(id)(
3 partition p1 values less than (10),
4 partition p2 values less than (20),
5 partition p3 values less than (30),
6 partition pmax values less than (maxvalue)
7 ); Table created.
插入数据
begin
for i in 1..50 loop
insert into scott.t_partition_range values(i);
end loop;
commit;
end;
/
模板复制
SYS@zkm> begin
2 for i in 1..50 loop
3 insert into scott.t_partition_range values(i);
4 end loop;
5 commit;
6 end;
7 / PL/SQL procedure successfully completed.
查询数据示例
SYS@zkm> select * from scott.t_partition_range partition(p1);
ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
SYS@zkm> select * from scott.t_partition_range partition(p2);
ID
----------
10
11
12
13
14
15
16
17
18
19
10 rows selected.
SYS@zkm> select * from scott.t_partition_range partition(pmax);
ID
----------
30
31
32
33
34
35
36
37
38
39
40
ID
----------
41
42
43
44
45
46
47
48
49
50
21 rows selected.
SYS@zkm> select * from scott.t_partition_range where rownum<=5;
ID
----------
1
2
3
4
5
数据泵导出
[oracle@oracle ~]$ expdp \' / as sysdba \' directory=dir dumpfile=test.dmp logfile=test.log reuse_dumpfiles=y cluster=n tables=scott.t_partition_range query=scott.t_partition_range:\"where rownum\<=5\" Export: Release 11.2.0.4. - Production on Thu May :: Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4. - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=dir dumpfile=test.dmp logfile=test.log reuse_dumpfiles=y cluster=n tables=scott.t_partition_range query=scott.t_partition_range:"where rownum<=5"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_PARTITION_RANGE":"P1" 5.046 KB rows
. . exported "SCOTT"."T_PARTITION_RANGE":"P2" 5.046 KB rows
. . exported "SCOTT"."T_PARTITION_RANGE":"P3" 5.046 KB rows
. . exported "SCOTT"."T_PARTITION_RANGE":"PMAX" 5.046 KB rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu May :: elapsed ::
可以看出，每个区分都导出了5行。
这...应该是BUG吧？