ITPub博客

首页 > 数据库 > Oracle > Oracle 12C新特性-在线把非分区表转为分区表

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

原创 Oracle 作者:chenoracle 时间:2020-02-12 22:54:45 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:/29785807/viewspace-2675347/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    276
  • 访问量
    810286