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;