vehiclealarm分区表

alter table vehiclealarm add month number(5);

update  vehiclealarm set month =  to_char(alarmTime,'MM');

 

SQL> exec dbms_redefinition.can_redef_table('gedb','vehiclealarm',1);

 

create table gedb.vehicleAlarm_bak (
  ALARMID              NUMBER(18)               NOT NULL,
  RULEID               VARCHAR2(36 BYTE)        NOT NULL,
  FDID                 VARCHAR2(18 BYTE)        NOT NULL,
  CHANNELTYPE          NUMBER(5)                NOT NULL,
  CHANNELID            NUMBER(5)                NOT NULL,
  FDNAME               VARCHAR2(100 BYTE),
  CHANNELNAME          VARCHAR2(100 BYTE),
  ALARMSESSIONID       NUMBER(10)               NOT NULL,
  ALARMSEQ             NUMBER(10)               NOT NULL,
  ALARMTYPE            NUMBER(5)                NOT NULL,
  ALARMTYPEMINOR       NUMBER(5),
  ALARMTIME            DATE                     NOT NULL,
  SEVERITYLEVEL        NUMBER(5),
  DETAIL               VARCHAR2(254 BYTE),
  CENTERSTOREKEY       VARCHAR2(254 BYTE),
  storageAreaId        VARCHAR2(60),
  PLATENO              VARCHAR2(20 BYTE),
  PLATENO2             VARCHAR2(20 BYTE),
  PLATETYPE            VARCHAR2(20 BYTE),
  PLATECOLOR           VARCHAR2(10 BYTE),
  PLATECHARACTERCOLOR  VARCHAR2(10 BYTE),
  VEHICLECOLOR         VARCHAR2(10 BYTE),
  VEHICLETYPE          VARCHAR2(20 BYTE),
  VEHICLELONG          NUMBER(5),
  RECORDEDSPEED        NUMBER(5),
  SPEEDMIN             NUMBER(5),
  SPEEDMAX             NUMBER(5),
  MONITORLOCATIONID    VARCHAR2(20 BYTE),
  MONITORLOCATIONNAME  VARCHAR2(100 BYTE),
  LONGITUDE            NUMBER(10),
  LATITUDE             NUMBER(10),
  MANAGERROLEID        VARCHAR2(18 BYTE),
  MANAGERROLENAME      VARCHAR2(40 BYTE),
  PLATENO3             VARCHAR2(20 BYTE),
  MONTH                NUMBER(5),
  constraint PK_VEHICLEOTHERALARM_bk primary key (alarmId)
)

partition by list(month)(
 partition p_vehicle_1 values(1),
 partition p_vehicle_2 values(2),
 partition p_vehicle_3 values(3),
 partition p_vehicle_4 values(4),
 partition p_vehicle_5 values(5),
 partition p_vehicle_6 values(6),
 partition p_vehicle_7 values(7),
 partition p_vehicle_8 values(8),
 partition p_vehicle_9 values(9),
 partition p_vehicle_10 values(10),
 partition p_vehicle_11 values(11),
 partition p_vehicle_12 values(12)
);

 

execute dbms_redefinition.start_redef_table('gedb','vehiclealarm','vehiclealarm_bak');

 

execute dbms_redefinition.sync_interim_table('gedb','vehiclealarm','vehiclealarm_bak');

 

execute dbms_redefinition.finish_redef_table('gedb','vehiclealarm','vehiclealarm_bak');

 

create index f_vehiclealarm_p ON vehiclealarm (plateno,plateno3,alarmtime desc,channelName,monitorLocationName)
local
(

partition vehicle_idx_01 tablespace USERS,

partition vehicle_idx_02 tablespace USERS,

partition vehicle_idx_03 tablespace USERS,

partition vehicle_idx_04 tablespace USERS,

partition vehicle_idx_05 tablespace USERS,

partition vehicle_idx_06 tablespace USERS,

partition vehicle_idx_07 tablespace USERS,

partition vehicle_idx_08 tablespace USERS,

partition vehicle_idx_09 tablespace USERS,

partition vehicle_idx_10 tablespace USERS,

partition vehicle_idx_11 tablespace USERS,

partition vehicle_idx_12 tablespace USERS

);

 

drop table vehiclealarm_bak;