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

维护组合分区:修改子分区模板、增加子分区、增加主分区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博客 ” ,链接:http://blog.itpub.net/20576390/viewspace-709345/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/20576390/viewspace-709345/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值