Oracle expdp导出分区表,query条件带有rownum
Oracle expdp导出分区表,query条件带有rownum
前言
在做数据脱敏的时候,对一张刚好是分区表的表做导出,为了只取出部分数据看是否数据可以正常脱敏,在query中带上rownum。
结果发现是每个分区都取出了rownum的限定行数。
比如:rownum<=5,正常去查询表的话是只会有5行的结果,
但是expdp导出分区表,带rownum<=5,则是每个分区都取出符合条件的5行。
这应该算BUG吧?
环境模拟
构造分区表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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吧?