Oracle expdp导出分区表，query条件带有rownum

 

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.0 - Production on Thu May 14 02:24:57 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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 "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: 32 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_PARTITION_RANGE":"P1"            5.046 KB       5 rows
. . exported "SCOTT"."T_PARTITION_RANGE":"P2"            5.046 KB       5 rows
. . exported "SCOTT"."T_PARTITION_RANGE":"P3"            5.046 KB       5 rows
. . exported "SCOTT"."T_PARTITION_RANGE":"PMAX"          5.046 KB       5 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 14 02:25:20 2020 elapsed 0 00:00:20

 

可以看出，每个区分都导出了5行。

 

这...应该是BUG吧？