Oracle将已有数据的表改为分区表【创建表分区、索引分区】

--我们以为Scada_data表建立分区为例，以下是为scada_data表加表分区【范围分区】的全过程

-- 创建临时表
create table scada_data_temp(

  scadad_id         VARCHAR2(20) not null  primary key,
  node_id           VARCHAR2(20) not null,
  dc_id             VARCHAR2(6) not null,
  eq_id             VARCHAR2(20) not null,
  eq_no             VARCHAR2(20),
  eq_name           NVARCHAR2(70),
  conn_point_node   VARCHAR2(20),
  conn_point_eq_id  VARCHAR2(20),
  conn_point_eq_no  VARCHAR2(20),
  station_node      VARCHAR2(20),
  st_id             VARCHAR2(20),
  slave_station_sn  NUMBER(10),
  pf_id             VARCHAR2(20),
  fc_id             VARCHAR2(20),
  collection_time   DATE not null,
  param_name        NVARCHAR2(50) not null,
  tp_id             VARCHAR2(20) not null,
  param_value       VARCHAR2(50),
  org_id            VARCHAR2(20) not null,
  version           INTEGER default 0 not null,
  data_units        VARCHAR2(20),
  alarm_state_id    VARCHAR2(6),
  alarm_upper_limit NUMBER(14,4),
  alarm_lower_limit NUMBER(14,4)

)
--这里我们选定collection_time为分区字段，建立范围分区，我们采用的都是自动分区的方式，我们指定仅需指定第一个分区和分区范围
--以后oracle会根据第一个分区的范围在有新的数据进来的时候创建新的分区
partition by range(collection_time)INTERVAL(NUMTOYMINTERVAL(1，'MONTH'))
 (
 partition p201507 values less than(to_date('2015-07-01','yyyy-MM-dd'))
     
);


-- Add comments to the table
comment on table scada_data_temp
  is 'SCADA采集数据';
-- Add comments to the columns
comment on column scada_data_temp.scadad_id
  is '采集数据ID';
comment on column scada_data_temp.node_id
  is '节点ID';
comment on column scada_data_temp.dc_id
  is '设备类型';
comment on column scada_data_temp.eq_id
  is '设备ID';
comment on column scada_data_temp.eq_no
  is '设备编号';
comment on column scada_data_temp.eq_name
  is '设备名称';
comment on column scada_data_temp.conn_point_node
  is '连接点节点';
comment on column scada_data_temp.conn_point_eq_id
  is '连接点设备ID';
comment on column scada_data_temp.conn_point_eq_no
  is '连接点设备编号';
comment on column scada_data_temp.station_node
  is '场站节点';
comment on column scada_data_temp.st_id
  is '场站ID';
comment on column scada_data_temp.slave_station_sn
  is '子站序号（485）';
comment on column scada_data_temp.pf_id
  is '协议功能ID';
comment on column scada_data_temp.fc_id
  is '标准功能ID ';
comment on column scada_data_temp.collection_time
  is '采集时间';
comment on column scada_data_temp.param_name
  is '参数名称（参数前缀+参数名称）';
comment on column scada_data_temp.tp_id
  is '参数模板ID';
comment on column scada_data_temp.param_value
  is '参数值';
comment on column scada_data_temp.org_id
  is '区分码';
comment on column scada_data_temp.version
  is '版本号';
comment on column scada_data_temp.data_units
  is '计量单位';
comment on column scada_data_temp.alarm_state_id
  is '报警状态ID';
comment on column scada_data_temp.alarm_upper_limit
  is '报警上限';
comment on column scada_data_temp.alarm_lower_limit
  is '报警下限';

 --向新表中插入数据，
  INSERT INTO scada_data_temp SELECT * FROM scada_data;


--这里我们保留原来的表，只执行重名操作
  alter table scada_data rename to scada_data_old;
  alter table scada_data_temp rename  to scada_data;
  --删除原来的索引，因为他们都是全局的
drop index IX_SCADA_DATA_COLLECTION_TIME ;

drop index IX_SCADA_DATA_CONN_POINT_EQ_ID ;
 
drop index IX_SCADA_DATA_CONN_POINT_EQ_NO ;
 
drop index IX_SCADA_DATA_CONN_POINT_NODE ;
 
drop index IX_SCADA_DATA_DC_ID ;
 
drop index IX_SCADA_DATA_EQ_ID ;
 
drop index IX_SCADA_DATA_EQ_NO ;
 
drop index IX_SCADA_DATA_FC_ID ;
 
drop index IX_SCADA_DATA_NODE_ID ;
 
drop index IX_SCADA_DATA_ORG_ID ;
 
drop index IX_SCADA_DATA_PF_ID ;
 
drop index IX_SCADA_DATA_STATION_NODE ;
 
drop index IX_SCADA_DATA_ST_ID ;

drop index IX_SCADA_DATA_TP_ID  ;
-- 创建分区索引
create index IX_SCADA_DATA_COLLECTION_TIME on SCADA_DATA (COLLECTION_TIME) LOCAL;
 
create index IX_SCADA_DATA_CONN_POINT_EQ_ID on SCADA_DATA (CONN_POINT_EQ_ID) LOCAL;
 
create index IX_SCADA_DATA_CONN_POINT_EQ_NO on SCADA_DATA (CONN_POINT_EQ_NO) LOCAL;
 
create index IX_SCADA_DATA_CONN_POINT_NODE on SCADA_DATA (CONN_POINT_NODE) LOCAL;
 
create index IX_SCADA_DATA_DC_ID on SCADA_DATA (DC_ID) LOCAL;
 
create index IX_SCADA_DATA_EQ_ID on SCADA_DATA (EQ_ID) LOCAL;
 
create index IX_SCADA_DATA_EQ_NO on SCADA_DATA (EQ_NO) LOCAL;
 
create index IX_SCADA_DATA_FC_ID on SCADA_DATA (FC_ID) LOCAL;
 
create index IX_SCADA_DATA_NODE_ID on SCADA_DATA (NODE_ID) LOCAL;
 
create index IX_SCADA_DATA_ORG_ID on SCADA_DATA (ORG_ID) LOCAL;
 
create index IX_SCADA_DATA_PF_ID on SCADA_DATA (PF_ID) LOCAL;
 
create index IX_SCADA_DATA_STATION_NODE on SCADA_DATA (STATION_NODE) LOCAL;
 
create index IX_SCADA_DATA_ST_ID on SCADA_DATA (ST_ID) LOCAL;

create index IX_SCADA_DATA_TP_ID on SCADA_DATA (TP_ID) LOCAL;

 --最后删除原来的表（如果没有必要的话，不建议执行删除操作，危险性较大)
 drop table scada_data;