【转】维护组合分区：修改子分区模板、增加子分区、增加主分区
维护组合分区：修改子分区模板、增加子分区、增加主分区2009-02-17 17:35新建的组合分区表中，子分区模板最好带上default值，否则不属于子分区范围内的数据插不进去：
U_TEST@ESUITE.NET SQL> CREATE TABLE customers_part
2 (
3 customer_id NUMBER (6),
4 cust_first_name VARCHAR2 (20),
5 cust_last_name VARCHAR2 (20),
6 nls_territory VARCHAR2 (30),
7 credit_limit NUMBER (9, 2)
8 )
9 PARTITION BY RANGE (credit_limit)
10 SUBPARTITION BY LIST (nls_territory)
11 SUBPARTITION TEMPLATE (
12 SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
13 SUBPARTITION west
14 VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND')
15 )
16 (PARTITION p1
17 VALUES LESS THAN (1000),
18 PARTITION p2
19 VALUES LESS THAN (2500),
20 PARTITION p3
21 VALUES LESS THAN (MAXVALUE));
Table created.
Elapsed: 00:00:00.18
U_TEST@ESUITE.NET SQL> INSERT INTO customers_part
2 VALUES (2,
3 'first1',
4 'last1',
5 'HONGKONG',
6 21);
INSERT INTO customers_part
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.03
修改子分区模板：
U_TEST@ESUITE.NET SQL> ALTER TABLE customers_part SET SUBPARTITION TEMPLATE
2 (SUBPARTITION east VALUES
3 ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
4 SUBPARTITION west VALUES
5 ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
6 SUBPARTITION other VALUES (DEFAULT));
Table altered.
Elapsed: 00:00:00.06
但不属于子分区范围内的数据还是插不进去：
U_TEST@ESUITE.NET SQL> INSERT INTO customers_part
2 VALUES (2,
3 'first1',
4 'last1',
5 'HONGKONG',
6 21);
INSERT INTO customers_part
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.01
原来修改分区模板并不会改变当前的分区结构，只有当你再增加、合并分区并且未显式指定子分区存储参数时，才会继承新分区模板中的参数：
U_TEST@ESUITE.NET SQL> SELECT table_name,
2 partition_name,
3 subpartition_name,
4 high_value
5 FROM DBA_TAB_SUBPARTITIONS
6 WHERE table_name LIKE 'CUSTOMERS_PART%'
7 order by 1,2;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
CUSTOMERS_PART P1 P1_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P1 P1_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P2 P2_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P2 P2_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P3 P3_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P3 P3_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
Elapsed: 00:00:00.06
对于已经存在的分区，只能一个个单独增加子分区了：
U_TEST@ESUITE.NET SQL> ALTER TABLE U_TEST.CUSTOMERS_PART MODIFY PARTITION P1 ADD SUBPARTITION P1_OTHER VALUES (DEFAULT);
Table altered.
Elapsed: 00:00:00.03
U_TEST@ESUITE.NET SQL> ALTER TABLE U_TEST.CUSTOMERS_PART MODIFY PARTITION P2 ADD SUBPARTITION P2_OTHER VALUES (DEFAULT);
Table altered.
Elapsed: 00:00:00.02
U_TEST@ESUITE.NET SQL> ALTER TABLE U_TEST.CUSTOMERS_PART MODIFY PARTITION P3 ADD SUBPARTITION P3_OTHER VALUES (DEFAULT);
Table altered.
Elapsed: 00:00:00.01
现在子分区信息就正常了：
U_TEST@ESUITE.NET SQL> SELECT table_name,
2 partition_name,
3 subpartition_name,
4 high_value
5 FROM DBA_TAB_SUBPARTITIONS
6 WHERE table_name LIKE 'CUSTOMERS_PART%'
7 ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
CUSTOMERS_PART P1 P1_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P1 P1_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P1 P1_OTHER DEFAULT
CUSTOMERS_PART P2 P2_OTHER DEFAULT
CUSTOMERS_PART P2 P2_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P2 P2_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P3 P3_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P3 P3_OTHER DEFAULT
CUSTOMERS_PART P3 P3_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
Elapsed: 00:00:00.03
也可以插入数据了：
U_TEST@ESUITE.NET SQL> INSERT INTO customers_part
2 VALUES (2,
3 'first1',
4 'last1',
5 'CHINA1',
6 21);
1 row created.
Elapsed: 00:00:00.00
U_TEST@ESUITE.NET SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
现在测试增加主分区，是否会正常套用刚改的子分区模板：
U_TEST@ESUITE.NET SQL> ALTER TABLE U_TEST.CUSTOMERS_PART SPLIT PARTITION P3 AT (4000) INTO (PARTITION P4, PARTITION P3);
Table altered.
Elapsed: 00:00:00.08
新增的分区自动套用了新加的子分区：
U_TEST@ESUITE.NET SQL> SELECT table_name,
2 partition_name,
3 subpartition_name,
4 high_value
5 FROM DBA_TAB_SUBPARTITIONS
6 WHERE table_name LIKE 'CUSTOMERS_PART%'
7 ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
CUSTOMERS_PART P1 P1_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P1 P1_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P1 P1_OTHER DEFAULT
CUSTOMERS_PART P2 P2_OTHER DEFAULT
CUSTOMERS_PART P2 P2_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P2 P2_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P3 P3_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P3 P3_OTHER DEFAULT
CUSTOMERS_PART P3 P3_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
CUSTOMERS_PART P4 P4_WEST 'AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'
CUSTOMERS_PART P4 P4_OTHER DEFAULT
CUSTOMERS_PART P4 P4_EAST 'CHINA', 'JAPAN', 'INDIA', 'THAILAND'
Elapsed: 00:00:00.00
U_TEST@ESUITE.NET SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20576390/viewspace-709345/ ，如需转载，请注明出处，否则将追究法律责任。
