RDBMS 11.2.0.4 

Before, the table was redefined online. The primary key mode is mainly used. This time, because there is no primary key on the table, it is done through rowid. The following is a simple example, which does not involve indexes, etc. (in fact, the index is also very simple, just set it to the tmp table and take it with you.)

The original table is as follows:

create table APPS.MQ_RECEIVE_LOG
(
  id               NUMBER not null,
  msg_id           VARCHAR2(100),
  calsign          VARCHAR2(20),
  msg_os           VARCHAR2(10),
  msg_type         VARCHAR2(10),
  msg_receive_time DATE,
  remark           VARCHAR2(500),
  msg_content      CLOB
)
tablespace USERS

1 check whether the checklist can be redefined Online

exec dbms_redefinition.can_redef_table('APPS','MQ_RECEIVE_LOG',dbms_redefinition.cons_use_rowid);

2 create middle table

create table APPS.MQ_RECEIVE_LOG_tmp
(
  id               NUMBER not null,
  msg_id           VARCHAR2(100),
  calsign          VARCHAR2(20),
  msg_os           VARCHAR2(10),
  msg_type         VARCHAR2(10),
  msg_receive_time DATE,
  remark           VARCHAR2(500),
  msg_content      CLOB
)
tablespace USERS
partition by range(msg_receive_time) interval(NUMTODSINTERVAL(7,'day'))
(
partition p20180925 values less than (to_date('2018-09-25','YYYY-MM-DD'))
)

3 start online redefinition. --Notice the null parameter in this.

exec dbms_redefinition.start_redef_table('APPS','MQ_RECEIVE_LOG','MQ_RECEIVE_LOG_tmp',null,dbms_redefinition.cons_use_rowid);

4. View the data of intermediate table and original table -- omitted

5 refresh data

exec dbms_redefinition.sync_interim_table('APPS','MQ_RECEIVE_LOG','MQ_RECEIVE_LOG_tmp');

6 check data - omitted

7 end online redefinition

exec dbms_redefinition.finish_redef_table('APPS','MQ_RECEIVE_LOG','MQ_RECEIVE_LOG_tmp');

8 view partition

select table_name,partition_name from dba_tab_partitions where table_name='MQ_RECEIVE_LOG';

Drop the middle table

For specific usage of DBMS "redefinition, please refer to the official SQL Package document.

Note: in 12c, there are not so many steps to do online redefinition, which can be done in one step. Please refer to previous blog s for details.

During redefinition, a large number of archive logs will be generated.

END