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

chenoracle

于 2024-12-19 21:43:53 发布

阅读量283

点赞数 2

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/144595552

版权

在这里插入图片描述

对比 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"