最初由 rushlight 发布
[B]Windows2000+Oracle9i
按照DBA Guide上的描述,将Hash分区表中的一个分区转换到另一范围-Hash分区表的分区中,但总是在最后一步报错。具体过程如下:
(1)创建一个hash分区表并加载一定的数据
SQL> CREATE TABLE t1 (i NUMBER, j NUMBER)
2 PARTITION BY HASH(i)
3 (PARTITION p1, PARTITION p2);
表已创建。
SQL> select table_name,partition_name
2 from user_tab_partitions where table_name='T1';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T1 P1
T1 P2
(2)创建一个范围—hash分区表
SQL> CREATE TABLE t2 (i NUMBER, j NUMBER)
2 PARTITION BY RANGE(j)
3 SUBPARTITION BY HASH(i)
4 (PARTITION p1 VALUES LESS THAN (10)
5 (SUBPARTITION t2_pls1,
6 SUBPARTITION t2_pls2),
7 PARTITION p2 VALUES LESS THAN (20)
8 (SUBPARTITION t2_p2s1,
9 SUBPARTITION t2_p2s2));
表已创建。
SQL> select table_name,partition_name,subpartition_name
2 from user_tab_subpartitions where table_name='T2';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
--------------- -------------------- --------------------
T2 P1 T2_PLS1
T2 P1 T2_PLS2
T2 P2 T2_P2S1
T2 P2 T2_P2S2
(3)将hash分区表exchange到范围hash分区表中
SQL> alter table t1 exchange partition p1 with table t2;
alter table t1 exchange partition p1 with table t2
*
ERROR 位于第 1 行:
ORA-14095: ALTER TABLE EXCHANGE 要求非分区, 非聚簇的表 [/B]