在线重定义实现普通表转分区表完整示例

--在线重定义实现普通表转分区表完整示例

--1、检验是否可以被重定义(在Command Window中执行)
exec dbms_redefinition.can_redef_table('GDEV','EV_CONNECTOR_STATUS_LOG');

--2、创建一个与原表字段相同,但是分区的表
create table ev_connector_status_log1
(
  log_no       number(16) not null,
  connector_id varchar2(256),
  operator_id  varchar2(256),
  status       varchar2(256),
  park_status  varchar2(256),
  lock_status  varchar2(256),
  modify_time  date,
  current_a    varchar2(256),
  current_b    varchar2(256),
  current_c    varchar2(256),
  voltage_a    varchar2(256),
  voltage_b    varchar2(256),
  voltage_c    varchar2(256),
  soc          varchar2(256)
)
 partition by range(modify_time)
 interval (numtoyminterval(1,'month'))
 (
   partition p_min values less than (to_date('2014-05-01','yyyy-mm-dd'))
 );

--3、开始在线重定义(在Command Window中执行)
exec dbms_redefinition.start_redef_table('GDEV', 'EV_CONNECTOR_STATUS_LOG', 'EV_CONNECTOR_STATUS_LOG1');

--4、拷贝有依赖关系的objects和statistics 
declare
  num_errors pls_integer;
begin
  dbms_redefinition.copy_table_dependents('GDEV',
                                          'EV_CONNECTOR_STATUS_LOG',
                                          'EV_CONNECTOR_STATUS_LOG1',
                                          dbms_redefinition.cons_orig_params,
                                          false,
                                          true,
                                          true,
                                          true,
                                          num_errors);
  dbms_output.put_line('errors := ' || to_char(num_errors));
end;
/

--可以通过下面命令查看，有哪些objects创建到临时表时失败。
select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

--5、同步数据(在Command Window中执行)
exec dbms_redefinition.sync_interim_table('GDEV', 'EV_CONNECTOR_STATUS_LOG', 'EV_CONNECTOR_STATUS_LOG1'); 

--6、结束在线重定义(在Command Window中执行)
exec dbms_redefinition.finish_redef_table('GDEV', 'EV_CONNECTOR_STATUS_LOG', 'EV_CONNECTOR_STATUS_LOG1'); 

--7、删除临时表
drop table ev_connector_status_log1;

--8、添加注释
comment on table EV_CONNECTOR_STATUS_LOG
  is '充电设备接口状态';
-- Add comments to the columns 
comment on column EV_CONNECTOR_STATUS_LOG.LOG_NO
  is '主键标识';
comment on column EV_CONNECTOR_STATUS_LOG.CONNECTOR_ID
  is '充电设备接口编码，同一运营商内唯一';
comment on column EV_CONNECTOR_STATUS_LOG.OPERATOR_ID
  is '运营商组织机构代码';
comment on column EV_CONNECTOR_STATUS_LOG.STATUS
  is '0：离网1：空闲2：占用（未充电）3：占用（充电中）4：占用（预约锁定）255：故障';
comment on column EV_CONNECTOR_STATUS_LOG.PARK_STATUS
  is '车位状态0：未知10：空闲50：占用';
comment on column EV_CONNECTOR_STATUS_LOG.LOCK_STATUS
  is '地锁状态0：未知10：已解锁50：已上锁';
comment on column EV_CONNECTOR_STATUS_LOG.MODIFY_TIME
  is '修改时间';
comment on column EV_CONNECTOR_STATUS_LOG.CURRENT_A
  is 'A相电流';
comment on column EV_CONNECTOR_STATUS_LOG.CURRENT_B
  is 'B相电流';
comment on column EV_CONNECTOR_STATUS_LOG.CURRENT_C
  is 'C相电流';
comment on column EV_CONNECTOR_STATUS_LOG.VOLTAGE_A
  is 'A相电压';
comment on column EV_CONNECTOR_STATUS_LOG.VOLTAGE_B
  is 'B相电压';
comment on column EV_CONNECTOR_STATUS_LOG.VOLTAGE_C
  is 'C相电压';
comment on column EV_CONNECTOR_STATUS_LOG.SOC
  is '剩余电量';

--9、如果重定义失败，解锁,此步骤不是必须(这步不执行)
exec dbms_redefinition.abort_redef_table('GDEV', 'EV_CONNECTOR_STATUS_LOG', 'EV_CONNECTOR_STATUS_LOG1');