create table RANGE_PART_TAB(id number,
deal_date date, area_code number, contents varchar2(4000))
partition by range(deal_date)
(
partition p1 values less than(to_date('2020-02-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2020-03-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2020-04-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2020-05-01','yyyy-mm-dd')),
partition p5 values less than(to_date('2020-06-01','yyyy-mm-dd')),
partition p6 values less than(to_date('2020-07-01','yyyy-mm-dd')),
partition p7 values less than(to_date('2020-08-01','yyyy-mm-dd')),
partition p8 values less than(to_date('2020-09-01','yyyy-mm-dd')),
partition p9 values less than(to_date('2020-10-01','yyyy-mm-dd')),
partition p10 values less than(to_date('2020-11-01','yyyy-mm-dd')));
insert into range_part_tab (id,deal_date,area_code,contents)selectrownum,
to_date(to_char(to_date('20200101','yyyymmdd'),'J')+ trunc(dbms_random.value(0,300)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')fromdual
connect by rownum<= 100000;
SQL>r 多次自插入数据1* insert into range_part_tab select * fromrange_part_tab800000rows created.
SQL>commit;
SQL> select sum(bytes)/1024/1024 from dba_segments where owner='SCOTT' and segment_name='RANGE_PART_TAB';
SUM(BYTES)/1024/1024
--------------------
792SQL> select PARTITION_NAME,sum(bytes)/1024/1024 from dba_segments where owner='SCOTT' and segment_name='RANGE_PART_TAB' group by
PARTITION_NAME order by 1;
PARTITION_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------P180P1072P280P380P480P580P680P780P880P980
10rows selected.
[oracle@test dump]$ expdp scott/tiger directory=dump dumpfile=D2%U.dmp tables=RANGE_PART_TAB parallel=10Worker1Status:
Process Name: DW00
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects:10Total Objects:10Completed Rows:138,592Completed Bytes:58,754,176Percent Done:77Worker Parallelism:1Worker2Status:
Process Name: DW01
State: WORK WAITING******************************************************************************Dump fileset for SCOTT.SYS_EXPORT_TABLE_01 is:/u01/dump/D201.dmp/u01/dump/D202.dmp/u01/dump/D203.dmp/u01/dump/D204.dmp/u01/dump/D205.dmp/u01/dump/D206.dmp/u01/dump/D207.dmp/u01/dump/D208.dmp/u01/dump/D209.dmp
Job"SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 06:26:04 2020 elapsed 0 00:00:22[oracle@test dump]$ expdp scott/tiger directory=dump dumpfile=D3%U.dmp tables=RANGE_PART_TAB
Job"SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 06:28:14 2020 elapsed 0 00:00:20 ！！！
测试了一个寂寞，根本不是想象中的十个不同的主进程，每个人负责一个分区，而还是一个主进程，串行化导出每个分区！ 很傻很天真。
？？？ 难道是对于每个Segments 大小 250M的限制？？？
对测试分区表扩大几倍。
666 果然是250MB的阈值，当单个分区SEGMENTS大于250M，才真正的开始了并行的作用，导出存在10个主进程导出。
SQL> select PARTITION_NAME,sum(bytes)/1024/1024 from dba_segments where owner='SCOTT' and segment_name='RANGE_PART_TAB' group by PARTITION_NAME;
PARTITION_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
P7 309
P4 304
P1 312
P2 288
P3 304
P6 296
P10 264
P9 300
P8 312
P5 312
10 rows selected.
[oracle@test dump]$ expdp scott/tiger directory=dump dumpfile=D4%U.dmp tables=RANGE_PART_TAB parallel=10
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 3 Status:
Process Name: DW02
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 4 Status:
Process Name: DW03
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 5 Status:
Process Name: DW04
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 6 Status:
Process Name: DW05
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 7 Status:
Process Name: DW06
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 8 Status:
Process Name: DW07
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 9 Status:
Process Name: DW08
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
Worker 10 Status:
Process Name: DW09
State: EXECUTING
Object Schema: SCOTT
Object Name: RANGE_PART_TAB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 10
Worker Parallelism: 1
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/dump/D401.dmp
/u01/dump/D402.dmp
/u01/dump/D403.dmp
/u01/dump/D404.dmp
/u01/dump/D405.dmp
/u01/dump/D406.dmp
/u01/dump/D407.dmp
/u01/dump/D408.dmp
/u01/dump/D409.dmp
/u01/dump/D410.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 06:37:20 2020 elapsed 0 00:00:40
[oracle@test dump]$ expdp scott/tiger directory=dump dumpfile=D5%U.dmp tables=RANGE_PART_TAB
测试下时间对比。
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 06:39:14 2020 elapsed 0 00:01:21
并行10，非并行时间 40s:80s=1:2 时间上减少了很多。数据越大，效率越明显。
转播
