Oracle 11g: 合并分区表-merge partition

Merging Partitions 使用ALTER TABLE .. MERGE PARTITION语句将两个分区的内容合并到另外一个分区，两个源分区和关联的local index都会被drop 不能用于 hash分区表或 hash subpartitions of a composite *-hash partitioned table 不能用于合并引用分区表（reference-partitioned table）合并范围分区允许合并两个临近范围的分区到另外分区，不相邻分区无法合并。合并结果分区继承两个源分区的最大边界。demo:-- Create a Table with four partitions each on its own tablespace
-- Partitioned by range on the data column.
CREATE TABLE four_seasons
（
one DATE,
two VARCHAR2（60）,
three NUMBER
）
PARTITION BY RANGE （ one ）
（
PARTITION quarter_one
VALUES LESS THAN （ TO_DATE（"01-apr-1998","dd-mon-yyyy"））
TABLESPACE quarter_one,PARTITION quarter_two
VALUES LESS THAN （ TO_DATE（"01-jul-1998","dd-mon-yyyy"））
TABLESPACE quarter_two,PARTITION quarter_three
VALUES LESS THAN （ TO_DATE（"01-oct-1998","dd-mon-yyyy"））
TABLESPACE quarter_three,PARTITION quarter_four
VALUES LESS THAN （ TO_DATE（"01-jan-1999","dd-mon-yyyy"））
TABLESPACE quarter_four）;-- Create local PREFIXED index on Four_Seasons
-- Prefixed because the leftmost columns of the index match the
-- Partitioning key CREATE INDEX i_four_seasons_l ON four_seasons （ one,two ）
LOCAL （
PARTITION i_quarter_one TABLESPACE i_quarter_one,
PARTITION i_quarter_two TABLESPACE i_quarter_two,
PARTITION i_quarter_three TABLESPACE i_quarter_three,
PARTITION i_quarter_four TABLESPACE i_quarter_four
）;下一步，合并分区.-- Merge the first two partitions
--
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;如果不显式声明 UPDATE INDEXES 语句,就必须给受影响的分区 rebuild the local index.-- Rebuild index for quarter_two, which has been marked unusable
-- because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
ALTER TABLE four_seasons MODIFY PARTITION
quarter_two REBUILD UNUSABLE LOCAL INDEXES;合并Interval Partitions

同RANGE一样，必须两个相邻分区才能合并
合并 List partitions则没有限制更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 11g: Invisible IndexesOracle的在线重定义表功能相关资讯 Oracle 11g merge partition

Oracle 11g导入到10g引起的错误（11/16/2015 10:55:27）
Oracle 11g 导库导不出空表问题（08/19/2015 19:55:58）
Oracle 11g统计信息收集--多列统计（07/24/2015 10:32:39）

Oracle 11gClone安装方法（08/24/2015 20:25:41）
Oracle 11g中和SQL TUNING相关的新（08/12/2015 11:22:52）
Oracle 11g数据泵详解（07/08/2015 08:29:51）

本文评论查看全部评论（0）