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;