oracle使用模版创建分区表

ORACLE 12C 中提供了使用 subpartition template 模版创建二级分区表，通过这种方法，不需要每个一级分区都需要写出其二级分区。

alter table HLB.***_ACCT modify partition by range (***_FLAG, ***_YM) subpartition by range (***_ORG_CODE) subpartition template (subpartition R33401 values less than ('33402') tablespace TBS_HLB compress , subpartition R33402 values less than ('33403') tablespace TBS_HLB compress , subpartition R33403 values less than ('33404') tablespace TBS_HLB compress , subpartition R33404 values less than ('33405') tablespace TBS_HLB compress , subpartition R33405 values less than ('33406') tablespace TBS_HLB compress , subpartition R33406 values less than ('33407') tablespace TBS_HLB compress , subpartition R33407 values less than ('33408') tablespace TBS_HLB compress , subpartition R33408 values less than ('33409') tablespace TBS_HLB compress , subpartition R33409 values less than ('33410') tablespace TBS_HLB compress , subpartition R33410 values less than ('33411') tablespace TBS_HLB compress , subpartition R33411 values less than ('33412') tablespace TBS_HLB compress , subpartition RMAX values less than (MAXVALUE) tablespace TBS_HLB compress ) ( partition runcleared values less than ('02', '209901') tablespace TBS_HLB compress , partition R03_201801 values less than ( '03', '201802' ) tablespace TBS_HLB compress , partition R03_201802 values less than ( '03', '201803' ) tablespace TBS_HLB compress , partition R03_201803 values less than ( '03', '201804' ) tablespace TBS_HLB compress , partition R03_201804 values less than ( '03', '201805' ) tablespace TBS_HLB compress , partition R03_201805 values less than ( '03', '201806' ) tablespace TBS_HLB compress , partition R03_201806 values less than ( '03', '201807' ) tablespace TBS_HLB compress , partition R03_201807 values less than ( '03', '201808' ) tablespace TBS_HLB compress , partition R03_201808 values less than ( '03', '201809' ) tablespace TBS_HLB compress, partition R03_201809 values less than ( '03', '201810' ) tablespace TBS_HLB compress, partition R03_201810 values less than ( '03', '201811' ) tablespace TBS_HLB compress, partition R03_201811 values less than ( '03', '201812' ) tablespace TBS_HLB compress, partition R03_201812 values less than ( '03', '201901' ) tablespace TBS_HLB compress, partition R03_201901 values less than ( '03', '201902' ) tablespace TBS_HLB compress , partition R03_201902 values less than ( '03', '201903' ) tablespace TBS_HLB compress , partition R03_201903 values less than ( '03', '201904' ) tablespace TBS_HLB compress , partition R03_201904 values less than ( '03', '201905' ) tablespace TBS_HLB compress , partition R03_201905 values less than ( '03', '201906' ) tablespace TBS_HLB compress , partition R03_201906 values less than ( '03', '201907' ) tablespace TBS_HLB compress , partition R03_201907 values less than ( '03', '201908' ) tablespace TBS_HLB compress , partition R03_201908 values less than ( '03', '201909' ) tablespace TBS_HLB compress, partition R03_201909 values less than ( '03', '201910' ) tablespace TBS_HLB compress, partition R03_201910 values less than ( '03', '201911' ) tablespace TBS_HLB compress, partition R03_201911 values less than ( '03', '201912' ) tablespace TBS_HLB compress, partition R03_201912 values less than ( '03', '202001' ) tablespace TBS_HLB compress, partition R03_202001 values less than ( '03', '202002' ) tablespace TBS_HLB compress , partition R03_202002 values less than ( '03', '202003' ) tablespace TBS_HLB compress , partition R03_202003 values less than ( '03', '202004' ) tablespace TBS_HLB compress , partition R03_202004 values less than ( '03', '202005' ) tablespace TBS_HLB compress , partition R03_202005 values less than ( '03', '202006' ) tablespace TBS_HLB compress , partition R03_202006 values less than ( '03', '202007' ) tablespace TBS_HLB compress , partition R03_202007 values less than ( '03', '202008' ) tablespace TBS_HLB compress , partition R03_202008 values less than ( '03', '202009' ) tablespace TBS_HLB compress, partition R03_202009 values less than ( '03', '202010' ) tablespace TBS_HLB compress, partition R03_202010 values less than ( '03', '202011' ) tablespace TBS_HLB compress, partition R03_202011 values less than ( '03', '202012' ) tablespace TBS_HLB compress, partition R03_202012 values less than ( '03', '202101' ) tablespace TBS_HLB compress, partition R03_202101 values less than ( '03', '202102' ) tablespace TBS_HLB compress , partition R03_202102 values less than ( '03', '202103' ) tablespace TBS_HLB compress , partition R03_202103 values less than ( '03', '202104' ) tablespace TBS_HLB compress , partition R03_202104 values less than ( '03', '202105' ) tablespace TBS_HLB compress , partition R03_202105 values less than ( '03', '202106' ) tablespace TBS_HLB compress , partition R03_202106 values less than ( '03', '202107' ) tablespace TBS_HLB compress , partition R03_202107 values less than ( '03', '202108' ) tablespace TBS_HLB compress , partition R03_202108 values less than ( '03', '202109' ) tablespace TBS_HLB compress, partition R03_202109 values less than ( '03', '202110' ) tablespace TBS_HLB compress, partition R03_202110 values less than ( '03', '202111' ) tablespace TBS_HLB compress, partition R03_202111 values less than ( '03', '202112' ) tablespace TBS_HLB compress, partition R03_202112 values less than ( '03', '202201' ) tablespace TBS_HLB compress, partition R03_202201 values less than ( '03', '202202' ) tablespace TBS_HLB compress , partition R03_202202 values less than ( '03', '202203' ) tablespace TBS_HLB compress , partition R03_202203 values less than ( '03', '202204' ) tablespace TBS_HLB compress , partition R03_202204 values less than ( '03', '202205' ) tablespace TBS_HLB compress , partition R03_202205 values less than ( '03', '202206' ) tablespace TBS_HLB compress , partition R03_202206 values less than ( '03', '202207' ) tablespace TBS_HLB compress , partition R03_202207 values less than ( '03', '202208' ) tablespace TBS_HLB compress , partition R03_202208 values less than ( '03', '202209' ) tablespace TBS_HLB compress, partition R03_202209 values less than ( '03', '202210' ) tablespace TBS_HLB compress, partition R03_202210 values less than ( '03', '202211' ) tablespace TBS_HLB compress, partition R03_202211 values less than ( '03', '202212' ) tablespace TBS_HLB compress, partition R03_202212 values less than ( '03', '202301' ) tablespace TBS_HLB compress, partition R03_202301 values less than ( '03', '202302' ) tablespace TBS_HLB compress , partition R03_202302 values less than ( '03', '202303' ) tablespace TBS_HLB compress , partition R03_202303 values less than ( '03', '202304' ) tablespace TBS_HLB compress , partition R03_202304 values less than ( '03', '202305' ) tablespace TBS_HLB compress , partition R03_202305 values less than ( '03', '202306' ) tablespace TBS_HLB compress , partition R03_202306 values less than ( '03', '202307' ) tablespace TBS_HLB compress , partition R03_202307 values less than ( '03', '202308' ) tablespace TBS_HLB compress , partition R03_202308 values less than ( '03', '202309' ) tablespace TBS_HLB compress, partition R03_202309 values less than ( '03', '202310' ) tablespace TBS_HLB compress, partition R03_202310 values less than ( '03', '202311' ) tablespace TBS_HLB compress, partition R03_202311 values less than ( '03', '202312' ) tablespace TBS_HLB compress, partition R03_202312 values less than ( '03', '202401' ) tablespace TBS_HLB compress, partition R03_202401 values less than ( '03', '202402' ) tablespace TBS_HLB compress , partition R03_202402 values less than ( '03', '202403' ) tablespace TBS_HLB compress , partition R03_202403 values less than ( '03', '202404' ) tablespace TBS_HLB compress , partition R03_202404 values less than ( '03', '202405' ) tablespace TBS_HLB compress , partition R03_202405 values less than ( '03', '202406' ) tablespace TBS_HLB compress , partition R03_202406 values less than ( '03', '202407' ) tablespace TBS_HLB compress , partition R03_202407 values less than ( '03', '202408' ) tablespace TBS_HLB compress , partition R03_202408 values less than ( '03', '202409' ) tablespace TBS_HLB compress, partition R03_202409 values less than ( '03', '202410' ) tablespace TBS_HLB compress, partition R03_202410 values less than ( '03', '202411' ) tablespace TBS_HLB compress, partition R03_202411 values less than ( '03', '202412' ) tablespace TBS_HLB compress, partition R03_202412 values less than ( '03', '202501' ) tablespace TBS_HLB compress, partition R03_202501 values less than ( '03', '202502' ) tablespace TBS_HLB compress , partition R03_202502 values less than ( '03', '202503' ) tablespace TBS_HLB compress , partition R03_202503 values less than ( '03', '202504' ) tablespace TBS_HLB compress , partition R03_202504 values less than ( '03', '202505' ) tablespace TBS_HLB compress , partition R03_202505 values less than ( '03', '202506' ) tablespace TBS_HLB compress , partition R03_202506 values less than ( '03', '202507' ) tablespace TBS_HLB compress , partition R03_202507 values less than ( '03', '202508' ) tablespace TBS_HLB compress , partition R03_202508 values less than ( '03', '202509' ) tablespace TBS_HLB compress, partition R03_202509 values less than ( '03', '202510' ) tablespace TBS_HLB compress, partition R03_202510 values less than ( '03', '202511' ) tablespace TBS_HLB compress, partition R03_202511 values less than ( '03', '202512' ) tablespace TBS_HLB compress, partition R03_202512 values less than ( '03', '202601' ) tablespace TBS_HLB compress, partition R03_202601 values less than ( '03', '202602' ) tablespace TBS_HLB , partition R03_202602 values less than ( '03', '202603' ) tablespace TBS_HLB , partition R03_202603 values less than ( '03', '202604' ) tablespace TBS_HLB , partition R03_202604 values less than ( '03', '202605' ) tablespace TBS_HLB , partition R03_202605 values less than ( '03', '202606' ) tablespace TBS_HLB , partition R03_202606 values less than ( '03', '202607' ) tablespace TBS_HLB , partition R03_202607 values less than ( '03', '202608' ) tablespace TBS_HLB , partition R03_202608 values less than ( '03', '202609' ) tablespace TBS_HLB , partition R03_202609 values less than ( '03', '202610' ) tablespace TBS_HLB , partition R03_202610 values less than ( '03', '202611' ) tablespace TBS_HLB , partition R03_202611 values less than ( '03', '202612' ) tablespace TBS_HLB , partition R03_202612 values less than ( '03', '202701' ) tablespace TBS_HLB , partition R03_202701 values less than ( '03', '202702' ) tablespace TBS_HLB , partition R03_202702 values less than ( '03', '202703' ) tablespace TBS_HLB , partition R03_202703 values less than ( '03', '202704' ) tablespace TBS_HLB , partition R03_202704 values less than ( '03', '202705' ) tablespace TBS_HLB , partition R03_202705 values less than ( '03', '202706' ) tablespace TBS_HLB , partition R03_202706 values less than ( '03', '202707' ) tablespace TBS_HLB , partition R03_202707 values less than ( '03', '202708' ) tablespace TBS_HLB , partition R03_202708 values less than ( '03', '202709' ) tablespace TBS_HLB , partition R03_202709 values less than ( '03', '202710' ) tablespace TBS_HLB , partition R03_202710 values less than ( '03', '202711' ) tablespace TBS_HLB , partition R03_202711 values less than ( '03', '202712' ) tablespace TBS_HLB , partition R03_202712 values less than ( '03', '202801' ) tablespace TBS_HLB , partition R03_202801 values less than ( '03', '202802' ) tablespace TBS_HLB , partition R03_202802 values less than ( '03', '202803' ) tablespace TBS_HLB , partition R03_202803 values less than ( '03', '202804' ) tablespace TBS_HLB , partition R03_202804 values less than ( '03', '202805' ) tablespace TBS_HLB , partition R03_202805 values less than ( '03', '202806' ) tablespace TBS_HLB , partition R03_202806 values less than ( '03', '202807' ) tablespace TBS_HLB , partition R03_202807 values less than ( '03', '202808' ) tablespace TBS_HLB , partition R03_202808 values less than ( '03', '202809' ) tablespace TBS_HLB , partition R03_202809 values less than ( '03', '202810' ) tablespace TBS_HLB , partition R03_202810 values less than ( '03', '202811' ) tablespace TBS_HLB , partition R03_202811 values less than ( '03', '202812' ) tablespace TBS_HLB , partition R03_202812 values less than ( '03', '202901' ) tablespace TBS_HLB , partition R08_209912 values less than ( '08', '209913' ) tablespace TBS_HLB ) online parallel 8;