【Oracle】Oracle在线重定义普通表为按月分区表

需求:
现用户usera下有一个普通表tab1因数据不断增加,计划改成按月分区表,需在不影响业务的情况下修改,故需要用到在线重定义。

表tab1特性:
SAMPLETIME以字符类型记录时间,如20200520101010,表无主键。

思路:按月分区,可用间隔分区,分区字段为SAMPLETIME
PARTITION BY RANGE (SAMPLETIME)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))

存在问题:
(1)SAMPLETIME是varchar类型,而间隔分区的分区字段需要是time/date类型。
(2)在线重定义表需要有主键,而此表无主键。

问题1方法有二:
(1)修改分区字段的类型为time/date类型
(2)创建一个sampletime的虚拟列,此虚拟列转换sampletime为date类型

问题2方法有一:
用rowid的方法在线重定义:Online redefinition of table using ROWID option (Doc ID 210407.1)

则测试如下:
方法一(在线重定义tab1,将其改为以sampletime字段分区的间隔分区表,同时sampletime要从varchar类型改为date类型):

(1)创建临时表

create table tab1_tmp
(
   sampletime date NOT NULL,
   text VARCHAR2(100)
)
PARTITION BY RANGE (sampletime) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2019-01-01', 'yyyy-mm-dd')),
partition part_t02 values less than(to_date('2019-02-01', 'yyyy-mm-dd')));

(2) check if the ROWID option can be used on this table.

USERA@PORCL>exec DBMS_REDEFINITION.can_REDEF_TABLE('usera','TBL_URL_CHKINFO',2);

PL/SQL procedure successfully completed.

(3)Initiate the reorganization process.

SQL> exec dbms_redefinition.start_redef_table('usera','tab1','tab1_tmp','to_date(sampletime) sampletime,text text',2)

–注意,要把所有字段都列出来,不能省略没有变化的。

(4)同步

BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('usera','tab1','tab1_tmp2');
END;
/

PL/SQL procedure successfully completed.

(5)Finish the reorganization process.

USERA@PORCL>exec dbms_redefinition.FINISH_REDEF_TABLE('usera','tab1','tab1_tmp');

PL/SQL procedure successfully completed.
USERA@PORCL>desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAMPLETIME                                NOT NULL DATE
 TEXT                                                   VARCHAR(100)

(5)检查分区信息

select * from dba_tab_partitions where table_name='TAB1';

select * from usera.TAB1 partition(SYS_P968);
select * from usera.TAB1 partition(PART_T02);

方法二(在线重定义tab1,创建一个sampletime的虚拟列,此虚拟列转换sampletime为date类型):

(1)创建临时表

CREATE TABLE tab1_tmp2
(
 SAMPLETIME          VARCHAR2(20)     NOT NULL,
 date_time as (to_date(SAMPLETIME,'yyyymmddhh24miss')),
 text VARCHAR2(100)
 )
PARTITION BY RANGE (sampletime) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2019-01-01', 'yyyy-mm-dd')),
partition part_t02 values less than(to_date('2019-02-01', 'yyyy-mm-dd')));

(2)Initiate the reorganization process.

SQL>exec dbms_redefinition.start_redef_table('usera','tab1','tab1_tmp2','sampletime sampletime,text text',2);

PL/SQL procedure successfully completed.

(3)同步

BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('usera','tab1','tab1_tmp2');
END;
/

PL/SQL procedure successfully completed.

(4)完成

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('usera','tab1','tab1_tmp2');
END;
/

PL/SQL procedure successfully completed.

注意:
(1)此表无约束、无索引,故在线重定义Copy dependent objects此部分操作省略,请见官方在线重定义的手册。
(2)在线重定义可修改原表字段类型,如varchar类型改为date类型可用to_data()实现

参考文档:
(1)https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11677
Database Administrator’s Guide——>20 Managing Tables——>Redefining Tables Online
(2)Online redefinition of table using ROWID option (Doc ID 210407.1)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值