|
刚才看到有同志说到Oracle9i中，不可以在线将普通表改为分区表，这个认识是错误的．下面我做个简单的demo，供参考
SQL> create table toms(region number(3),oid number(14)); 表已创建。 SQL> alter table toms add constraint pk_toms primary key(region,oid); 表已更改。 SQL> select * from user_tab_partitions where table_name='TOMS'; TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ---------- SQL> 这时候不是分区表，下面再插入点数据 SQL> insert into toms values(100,10001); 已创建 1 行。 SQL> insert into toms values(200,10002); 已创建 1 行。 SQL> insert into toms values(300,10003); 已创建 1 行。 SQL> commit; 提交完成。 SQL> 建立中间表
SQL> create table toms_mid
2 (
3 region number(3),
4 oid number(14)
5 )
6 partition by range(region)
7 (partition p_100 values less than(101),
8 partition p_200 values less than(201),
9 partition p_300 values less than(301),
10 partition p_max values less than(999)
11 ); 表已创建。 SQL> select * from user_tab_partitions where table_name='TOMS_MID'; TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
TOMS_MID NO P_100 0 101 3 1 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS_MID NO P_200 0 201 3 2 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS_MID NO P_300 0 301 3 3 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS_MID NO P_MAX 0 999 3 4 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO SQL> 看清楚，这个时候toms_mid是分区表，下面给toms_mid加个主键 SQL> alter table toms_mid add constraint pk_toms_mid
2 primary key(region,oid)
3 using index(
4 create index pk_toms_mid on toms_mid(region,oid)
5 local
6 (partition p_100,
7 partition p_200,
8 partition p_300,
9 partition p_max
10 )
11 )
12 / 表已更改。 SQL> 开始在线重定义
SQL> exec dbms_redefinition.start_redef_table('STUDY','TOMS','TOMS_MID'); PL/SQL 过程已成功完成。 SQL> exec dbms_redefinition.finish_redef_table('STUDY','TOMS','TOMS_MID'); PL/SQL 过程已成功完成。
定义完成，检查一下
SQL> select * from toms;
REGION OID
---------- ----------
100 10001
200 10002
300 10003 已选择3行。 SQL> select * from toms_mid; REGION OID
---------- ----------
100 10001
200 10002
300 10003 已选择3行。 SQL>
SQL> select * from user_tab_partitions where table_name='TOMS';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
TOMS NO P_100 0 101 3 1 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS NO P_200 0 201 3 2 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS NO P_300 0 301 3 3 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO
TOMS NO P_MAX 0 999 3 4 STUDY 10 40 1 255 1048576 1048576 1 2147483645 0 1 1 YES DISABLED DEFAULT NO NO SQL>
表已经被改成分区表了 SQL> select * from user_tab_partitions where table_name='TOMS_MID'; TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ---------- SQL>
|
