非分区表=>分区表（一）

大概步骤：
1.创建原始表SRC及索引
2.插入数据
3.创建目标表DST及索引（要求和原表结构一模一样，多一个列也不行）
4.交换分区（第3步创建分区表DST时只指定一个分区）
5.验证结果
6.将DST按规则再一分为三个小分区（有点像装操作系统时给硬盘分区）
 
1.创建原始表SRC及索引

A>create table src(id int);

Table created.

A>create index src_ind on src(id);

Index created.

A>alter table src add constraint src_pk primary key (id);

Table altered.

2.插入数据

A>begin
  2  for i in 1..10000 loop
  3  insert into src values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

A>select INDEX_NAME,STATUS from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SRC_IND                        VALID

3.创建目标表DST及索引（要求和原表结构一模一样，多一个列也不行）
--这里初始化只创建了一个分区

A>create table dst(id int)
  2  partition by range(id)
  3  (partition p3 values less than(maxvalue))
   4  /

Table created.

A>create index dst_ind on dst(id);

Index created.

A>alter table dst add constraint dst_pk primary key(id);

Table altered.

如果两个表的结构不完全一样的话，比如DST比SRC多一列，那么在交换时就会报这个错误
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

A>select INDEX_NAME,STATUS from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SRC_IND                        VALID
DST_IND                        VALID


4.交换分区（第3步创建分区表DST时只指定一个分区）

A>alter table dst exchange partition p3 with table src with validation update global indexes;

Table altered.

--注意交换完分区后，源表索引状态变为"不可用"（为啥我还不清楚）
A>select INDEX_NAME,STATUS from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SRC_IND                        UNUSABLE
DST_IND                        VALID

5.查看结果

A>select count(*) from src;

  COUNT(*)
----------
         0

A>select count(*) from dst;

  COUNT(*)
----------
     10000

对表DST进行分析后查看

A>select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
DST                            P3                                  10000

如果交换分区时，没有指定update global indexes，那么在对表进行分析时会报这个错误

A>EXEC DBMS_STATS.gather_table_stats(user,'dst',cascade=>true);
BEGIN DBMS_STATS.gather_table_stats(user,'dst',cascade=>true); END;

*
ERROR at line 1:
ORA-20000: index "A"."DST_IND"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

6.将DST再一分为三

A>alter table dst split partition p3 at (3000) into (partition p1,partition p3) update global indexes;

Table altered.

A>alter table dst split partition p3 at (6000) into (partition p2,partition p3) update global indexes;

Table altered.

A>select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
DST                            P2                                   3000
DST                            P1                                   2999
DST                            P3                                   4001

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29646619/viewspace-1165319/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29646619/viewspace-1165319/