oracle range分区表已经有了MAXVALUE 分区,如何添加分区?要不能删除MAXVALUE分区里的数据,不影响在线应用。
SQL> create table p_range_test 2 (id number,name varchar2(100)) 3 partition by range(id)( 4 partition t_p1 values less than (10), 5 partition t_p2 values less than (20), 6 partition t_p3 values less than (30) 7 ); Table created.
SQL> col TABLE_NAME for a20 SQL> col partition_name for a20 SQL> col HIGH_VALUE for a10 SQL> col TABLESPACE_NAME for a15 SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- --------------- P_RANGE_TEST T_P1 10 TP1 P_RANGE_TEST T_P2 20 TP1 P_RANGE_TEST T_P3 30 TP1 从上面看出已有3个分区
SQL> alter table p_range_test add partition t_p4 values less than(40); Table altered. 顺序添加完成,当然这个不是LZ想要的吧?
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- --------------- P_RANGE_TEST T_P1 10 TP1 P_RANGE_TEST T_P2 20 TP1 P_RANGE_TEST T_P3 30 TP1 P_RANGE_TEST T_P4 40 TP1 从这个实验结果可以看到t_p4分区已经创建成功
继续实验来验证是否可行 !!!
SQL> drop table p_range_test purge; create table p_range_test (id number,name varchar2(100)) partition by range(id)( partition t_p1 values less than (10), partition t_p2 values less than (20), partition t_p3 values less than (30), partition t_pmax values less than (maxvalue)); Table dropped. SQL> 2 3 4 5 6 Table created. 7、此时添加分区时会报如下的错误 SQL> alter table p_range_test add partition t_p4 values less than(40); alter table p_range_test add partition t_p4 values less than(40) * ERROR at line 1: ORA-14074: partition bound must collate higher than that of the last partition 添加分区失败?那有没有办法搞定呢,对于牛B的oracle来说肯定有办法了,处理方法就是使用split来搞!
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- --------------- P_RANGE_TEST T_P1 10 TP1 P_RANGE_TEST T_P2 20 TP1 P_RANGE_TEST T_P3 30 TP1 P_RANGE_TEST T_PMAX MAXVALUE TP1 9、注意,这步就是关键的一步!!!!!使用split完成上面没有完成的分区任务 SQL> alter table p_range_test split partition t_pmax at (40) into (partition, partition t_pmax); Table altered. 10.再查一下当前的分表的信息 SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- --------------- P_RANGE_TEST T_P1 10 TP1 P_RANGE_TEST T_P2 20 TP1 P_RANGE_TEST T_P3 30 TP1 P_RANGE_TEST SYS_P41 40 TP1 P_RANGE_TEST T_PMAX MAXVALUE TP1 看到想要的查询结果到此就可以收工了,哈哈哈!!!
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!********** QQ: 252803295 Email: oracledba_cn@hotmail.com Blog: http://blog.csdn.net/guoyJoe ITPUB: http://www.itpub.net/space-uid-28460966.html OCM: http://education.oracle.com/education/otn/YGuo.HTM 答案在:http://blog.csdn.net/guoyjoe/article/details/8624392 Oracle@Paradise 总群:127149411 Oracle@Paradise No.1群:177089463(已满) Oracle@Paradise No.2群:121341761 Oracle@Paradise No.3群:140856036 |