Oracle 12C新特性-在线把非分区表转为分区表

来源：这里教程网时间：2026-03-03 15:00:22 作者：

Oracle 12C 新特性 - 在线把非分区表转为分区表

对比 Oracle 11G 、 12C 在线将非分区表转换成分区表 一：oracle 11G 在线将非分区表转换为分区表 Online Redefinition 二：Oracle 12C 在线将非分区表转换为分区表 ALTER TABLE table_name MODIFY table_partitioning_clauses [ filter_condition ] [ ONLINE ] [ UPDATE INDEXES [ ( index { local_partitioned_index | global_partitioned_index | GLOBAL } [, index { local_partitioned_index | global_partitioned_index | GLOBAL } ]... ) ] ] 一：oracle 11G 将非分区表转换为分区表 在线重定义Online Redefinition 1. 数据库版本 --- 数据库 19C ，相当于 12.2.0.3 版本，也支持 Online Redefinition SQL> select banner_full from v$version; BANNER_FULL --------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CJCPDB READ WRITE NO 2. 创建测试表插入测试数据 SQL> conn cjc/cjc@cjcpdb SQL> create table t1(id number,adr varchar2(100),acc number); SQL> insert into t1 values(1,'dapuchai',10); insert into t1 values(2,'dunhua',20); insert into t1 values(3,'xiaopuchai',30); insert into t1 values(4,'fuerhe',101); insert into t1 values(5,'fuyuanjie',130); insert into t1 values(6,'songyuanjie',125); insert into t1 values(7,'bajiazhi',166); insert into t1 values(8,'yaotun',105); insert into t1 values(9,'hanconggou',256); insert into t1 values(10,'jiangdong',270); commit; SQL> alter table t1 add constraint pk_t1_id primary key (id); SQL> col adr for a15 SQL> select * from t1; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 4 fuerhe 101 5 fuyuanjie 130 6 songyuanjie 125 7 bajiazhi 166 8 yaotun 105 9 hanconggou 256 10 jiangdong 270 10 rows selected. 3. 检查下这张表是否可以在线重定义 ---dbms_redefinition.cons_use_rowid ---dbms_redefinition.cons_use_pk SQL> exec dbms_redefinition.can_redef_table( 'CJC','T1',dbms_redefinition.cons_use_pk); PL/SQL procedure successfully completed. 4. 建立在线重定义需要的中间表 SQL> create table t1_temp(id number,adr varchar2(100),acc number) partition by range(acc)( partition PAR01 values less than (100), partition PAR02 values less than (200), partition PAR03 values less than (300), partition PARMAX values less THAN (MAXVALUE) ); SQL> alter table t1_temp add constraint pk_t1_temp_id1 primary key (id); 5. 启动在线重定义 SQL> exec dbms_redefinition.start_redef_table('CJC', 'T1', 'T1_TEMP'); PL/SQL procedure successfully completed. 6. 检查中间表数据 SQL> select * from t1_temp; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 4 fuerhe 101 5 fuyuanjie 130 6 songyuanjie 125 7 bajiazhi 166 8 yaotun 105 9 hanconggou 256 10 jiangdong 270 10 rows selected. 7. 模拟生产环境数据变化 SQL> insert into t1 values(20,'yansan',208); 1 row created. SQL> delete t1 where id=4; 1 row deleted. SQL> update t1 set adr='dashitou' where id=7; 1 row updated. SQL> commit; Commit complete. 8. 原表被修改，中间表并没有更新 SQL> select * from t1; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 5 fuyuanjie 130 6 songyuanjie 125 7 dashitou 166 8 yaotun 105 9 hanconggou 256 10 jiangdong 270 20 yansan 208 10 rows selected. SQL> select * from t1_temp; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 4 fuerhe 101 5 fuyuanjie 130 6 songyuanjie 125 7 bajiazhi 166 8 yaotun 105 9 hanconggou 256 10 jiangdong 270 10 rows selected. 9. 中间表同步数据 SQL> exec dbms_redefinition.sync_interim_table('CJC', 'T1', 'T1_TEMP'); PL/SQL procedure successfully completed. 查询同步后数据： SQL> select * from t1_temp; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 5 fuyuanjie 130 6 songyuanjie 125 8 yaotun 105 7 dashitou 166 9 hanconggou 256 10 jiangdong 270 20 yansan 208 10 rows selected. 10. 结束在线重定义 SQL> exec dbms_redefinition.finish_redef_table('CJC', 'T1', 'T1_TEMP'); PL/SQL procedure successfully completed. 11. 验证数据 SQL> select * from T1; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 5 fuyuanjie 130 6 songyuanjie 125 7 dashitou 166 8 yaotun 105 9 hanconggou 256 10 jiangdong 270 20 yansan 208 10 rows selected. SQL> select * from t1_temp; ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 5 fuyuanjie 130 6 songyuanjie 125 8 yaotun 105 7 dashitou 166 9 hanconggou 256 10 jiangdong 270 20 yansan 208 10 rows selected. 12. 查看各分区数据 SQL> col table_name for a10 SQL> col partition_name for a10 SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1'; TABLE_NAME PARTITION_ ---------- ---------- T1 PAR01 T1 PAR02 T1 PAR03 T1 PARMAX SQL> select * from T1 partition(PAR01); ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 SQL> select * from T1 partition(PAR02); ID ADR ACC ---------- --------------- ---------- 5 fuyuanjie 130 6 songyuanjie 125 8 yaotun 105 7 dashitou 166 SQL> select * from T1 partition(PAR03); ID ADR ACC ---------- --------------- ---------- 9 hanconggou 256 10 jiangdong 270 20 yansan 208 13. 检查并删掉中间表 SQL> drop table t1_temp purge; Table dropped. 二：Oracle 12C 将非分区表转换为分区表 在12C 中在线将非分区表转换为分区表要相对11G容易了许多， 只需要一条语句即可搞定:ALTER TABLE table_name MODIFY table_partitioning_clauses ...... 1. 创建测试表入测试数据 SQL> conn cjc/cjc@cjcpdb ---drop table t1 purge; SQL> create table t1(id number,adr varchar2(100),acc number); insert into t1 values(1,'dapuchai',10); insert into t1 values(2,'dunhua',20); insert into t1 values(3,'xiaopuchai',30); insert into t1 values(4,'fuerhe',101); insert into t1 values(5,'fuyuanjie',130); insert into t1 values(6,'songyuanjie',125); insert into t1 values(7,'bajiazhi',166); insert into t1 values(8,'yaotun',105); insert into t1 values(9,'hanconggou',256); insert into t1 values(10,'jiangdong',270); commit; SQL> alter table t1 add constraint pk_t1_id primary key (id); SQL> col adr for a15 SQL> select * from t1; SQL> col table_name for a10 SQL> col partition_name for a10 SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1'; no rows selected 2. 在线将非分区表转换为分区表，索引转换成全局索引 SQL> alter table t1 modify partition by range (acc) ( partition PAR01 values less than (100), partition PAR02 values less than (200), partition PAR03 values less than (300), partition PARMAX values less than (MAXVALUE) ) online update indexes ( pk_t1_id GLOBAL ); Table altered. 3. 检查 SQL> col table_name for a10 SQL> col partition_name for a10 SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1'; TABLE_NAME PARTITION_ ---------- ---------- T1 PAR01 T1 PAR02 T1 PAR03 T1 PARMAX SQL> select * from T1 partition(PAR01); ID ADR ACC ---------- --------------- ---------- 1 dapuchai 10 2 dunhua 20 3 xiaopuchai 30 SQL> select * from T1 partition(PAR02); ID ADR ACC ---------- --------------- ---------- 4 fuerhe 101 5 fuyuanjie 130 6 songyuanjie 125 7 bajiazhi 166 8 yaotun 105 SQL> select * from T1 partition(PAR03); ID ADR ACC ---------- --------------- ---------- 9 hanconggou 256 10 jiangdong 270 SQL> col index_name for a15 SQL> col index_type for a10 SQL> select index_name,index_type,status from user_indexes; INDEX_NAME INDEX_TYPE STATUS --------------- ---------- -------- PK_T1_ID NORMAL VALID SQL> col segment_name for a15 SQL> select segment_name,segment_type from user_segments; SEGMENT_NAME SEGMENT_TYPE --------------- ------------------ PK_T1_ID INDEX T1 TABLE PARTITION T1 TABLE PARTITION T1 TABLE PARTITION T1 TABLE PARTITION 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！