【转】维护组合分区：修改子分区模板、增加子分区、增加主分区

最新推荐文章于 2021-04-04 11:06:59 发布

cjeoe86644

最新推荐文章于 2021-04-04 11:06:59 发布

阅读量169

点赞数

维护组合分区：修改子分区模板、增加子分区、增加主分区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

新增的分区自动套用了新加的子分区：