-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
2.2 增加表分区
增加分区的分区范围必须比当前分区的最后一个分区更高
TYGER@ORA11G>alter table sales add partitionsales01 values less than ('01-Feb-2004') tablespace sales_ts01;
alter table sales add partition sales01 values lessthan ('01-Feb-2004') tablespace sales_ts01
*
ERROR at line 1:
ORA-14074: partition bound mustcollate higher than that of the last partition
TYGER@ORA11G>alter table sales add partitionsales01
2 values less than ('01-Aug-2004') tablespacesales_ts01;
Table altered.
TYGER@ORA11G>selecttable_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
SALES SALES01 SALES_TS01
2.3 合并分区
· 必须是相邻的范围分区
· 继承最大的范围边界
TYGER@ORA11G>alter table sales
2 merge partitions sales06,sales01 intopartition sales07;
Table altered.
TYGER@ORA11G>select table_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES07 USERS
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
2.4 移动表分区
· 移动分区数据到另一个表空间
· 重新整理数据减少碎片
· 改变物理属性
TYGER@ORA11G>alter table sales move partitionsales01
2 tablespace sales_ts_move;
2.5 拆分表分区
拆分表分区———一个分区变的太大，导致备份，恢复和分区性能操作花费时间太长，重新分配I/O负载。
TYGER@ORA11G>alter table sales split partition sales07
2 at ('01-Jul-2004') // 按哪个时间点拆分
3 into (partition sales01 tablespacesales_ts01,
4 partition sales06 tablespacesales_ts06);
Table altered.
TYGER@ORA11G>selecttable_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
SALES SALES01 SALES_TS01
6 rows selected.