189 8069 5689

在线重定义?普通表转换成分区表

--收集表的统计信息
exec dbms_stats.gather_table_stats('kcpt', 'TH_VEHICLE_MEDIA', cascade => true);
--创建临时分区表
CREATE TABLE KCPT.TEMP_MEDIA
(
  MEDIA_ID            VARCHAR2(100 BYTE)        NOT NULL,
  VID                 NUMBER(15)                NOT NULL,
  DEVICE_NO           VARCHAR2(20 BYTE),
  MTYPE_CODE          VARCHAR2(20 BYTE),
  MFORMAT_CODE        VARCHAR2(20 BYTE),
  EVENT_TYPE          VARCHAR2(20 BYTE),
  UTC                 NUMBER(15),
  MEDIA_URI           VARCHAR2(200 BYTE),
  LENS_NO             VARCHAR2(10 BYTE),
  FILE_SIZE           NUMBER(10),
  DIMENSION           VARCHAR2(20 BYTE),
  FILE_TYPE           VARCHAR2(20 BYTE),
  SAMPLE_RATE         NUMBER(5),
  LAT                 NUMBER,
  LON                 NUMBER,
  MAPLON              NUMBER,
  MAPLAT              NUMBER,
  ELEVATION           NUMBER(10),
  DIRECTION           NUMBER(10),
  GPS_SPEED           NUMBER(10),
  STATUS_CODE         VARCHAR2(200 BYTE),
  ALARM_CODE          VARCHAR2(200 BYTE),
  SYSUTC              NUMBER(15),
  IS_OVERLOAD         NUMBER(2)                 DEFAULT 0,
  EVENT_STATUS        NUMBER(2),
  ENABLE_FLAG         VARCHAR2(2 BYTE),
  SEQ                 VARCHAR2(100 BYTE),
  SEND_USER           NUMBER(10),
  EVENTID             VARCHAR2(10 BYTE),
  MEMO                VARCHAR2(500 BYTE),
  MULT_MEDIA_ID       VARCHAR2(100 BYTE),
  EVENT_TRIGGER_TIME  NUMBER(15),
  READ_FLAG           VARCHAR2(2 BYTE)          DEFAULT 0,
  OVERLOAD_NUM        NUMBER(5),
  OVERLOAD_BY         NUMBER(15),
  OVERLOAD_TIME       NUMBER(15),
  SUPPLEMENTAL LOG GROUP GGS_TH_VEHICLE_MEDI_168340 (MEDIA_ID) ALWAYS
)
TABLESPACE KCPT_DATA
PARTITION BY RANGE (UTC)
( 
PARTITION P_MEDIA_FIRST VALUES LESS THAN (1359561600000)
,
  PARTITION P_MEDIA20130201 VALUES LESS THAN (1359648000000)
,
  PARTITION P_MEDIA20130202 VALUES LESS THAN (1359734400000)
,
  PARTITION P_MEDIA20130203 VALUES LESS THAN (1359820800000)
,
  PARTITION P_MEDIA20130204 VALUES LESS THAN (1359907200000)
,
  PARTITION P_MEDIA20130205 VALUES LESS THAN (1359993600000)
,
  PARTITION P_MEDIA20130206 VALUES LESS THAN (1360080000000)
,
  PARTITION P_MEDIA20130207 VALUES LESS THAN (1360166400000)
,
  PARTITION P_MEDIA20130208 VALUES LESS THAN (1360252800000)
,
  PARTITION P_MEDIA20130209 VALUES LESS THAN (1360339200000)
,
  PARTITION P_MEDIA20130210 VALUES LESS THAN (1360425600000)
,
  PARTITION P_MEDIA20130211 VALUES LESS THAN (1360512000000)
,
  PARTITION P_MEDIA20130212 VALUES LESS THAN (1360598400000)
,
PARTITION P_MEDIA20130213 VALUES LESS THAN (1360684800000)
,
PARTITION P_MEDIA20130214 VALUES LESS THAN (1360771200000)
,
PARTITION P_MEDIA20130215 VALUES LESS THAN (1360857600000)
,
PARTITION P_MEDIA20130216 VALUES LESS THAN (1360944000000)
,
PARTITION P_MEDIA20130217 VALUES LESS THAN (1361030400000)
,
PARTITION P_MEDIA20130218 VALUES LESS THAN (1361116800000)
,
PARTITION P_MEDIA20130219 VALUES LESS THAN (1361203200000)
,
PARTITION P_MEDIA20130220 VALUES LESS THAN (1361289600000)
,
PARTITION P_MEDIA20130221 VALUES LESS THAN (1361376000000)
,
PARTITION P_MEDIA20130222 VALUES LESS THAN (1361462400000)
,
PARTITION P_MEDIA20130223 VALUES LESS THAN (1361548800000)
,
PARTITION P_MEDIA20130224 VALUES LESS THAN (1361635200000)
,
PARTITION P_MEDIA20130225 VALUES LESS THAN (1361721600000)
,
PARTITION P_MEDIA20130226 VALUES LESS THAN (1361808000000)
,
PARTITION P_MEDIA20130227 VALUES LESS THAN (1361894400000)
,
PARTITION P_MEDIA20130228 VALUES LESS THAN (1361980800000)
,
PARTITION P_MEDIA20130301 VALUES LESS THAN (1362067200000)
,
PARTITION P_MEDIA20130302 VALUES LESS THAN (1362153600000)
,
PARTITION P_MEDIA20130303 VALUES LESS THAN (1362240000000)
,
PARTITION P_MEDIA20130304 VALUES LESS THAN (1362326400000)
,
PARTITION P_MEDIA20130305 VALUES LESS THAN (1362412800000)
,
PARTITION P_MEDIA20130306 VALUES LESS THAN (1362499200000)
,
PARTITION P_MEDIA20130307 VALUES LESS THAN (1362585600000)
,
PARTITION P_MEDIA20130308 VALUES LESS THAN (1362672000000)
,
PARTITION P_MEDIA20130309 VALUES LESS THAN (1362758400000)
,
PARTITION P_MEDIA20130310 VALUES LESS THAN (1362844800000)
,
PARTITION P_MEDIA20130311 VALUES LESS THAN (1362931200000)
,
PARTITION P_MEDIA20130312 VALUES LESS THAN (1363017600000)
,
PARTITION P_MEDIA20130313 VALUES LESS THAN (1363104000000)
,
PARTITION P_MEDIA20130314 VALUES LESS THAN (1363190400000)
,
PARTITION P_MEDIA20130315 VALUES LESS THAN (1363276800000)
,
PARTITION P_MEDIA20130316 VALUES LESS THAN (1363363200000)
,
PARTITION P_MEDIA20130317 VALUES LESS THAN (1363449600000)
,
PARTITION P_MEDIA20130318 VALUES LESS THAN (1363536000000)
,
PARTITION P_MEDIA20130319 VALUES LESS THAN (1363622400000)
,
PARTITION P_MEDIA20130320 VALUES LESS THAN (1363708800000)
,
PARTITION P_MEDIA20130321 VALUES LESS THAN (1363795200000)
,
PARTITION P_MEDIA20130322 VALUES LESS THAN (1363881600000)
,
PARTITION P_MEDIA20130323 VALUES LESS THAN (1363968000000)
,
PARTITION P_MEDIA20130324 VALUES LESS THAN (1364054400000)
,
PARTITION P_MEDIA20130325 VALUES LESS THAN (1364140800000)
,
PARTITION P_MEDIA20130326 VALUES LESS THAN (1364227200000)
,
PARTITION P_MEDIA20130327 VALUES LESS THAN (1364313600000)
,
PARTITION P_MEDIA20130328 VALUES LESS THAN (1364400000000)
,
PARTITION P_MEDIA20130329 VALUES LESS THAN (1364486400000)
,
PARTITION P_MEDIA20130330 VALUES LESS THAN (1364572800000)
,
PARTITION P_MEDIA20130331 VALUES LESS THAN (1364659200000)
,
PARTITION P_MEDIA20130401 VALUES LESS THAN (1364745600000)
,
PARTITION P_MEDIA20130402 VALUES LESS THAN (1364832000000)
,
PARTITION P_MEDIA20130403 VALUES LESS THAN (1364918400000)
,
PARTITION P_MEDIA20130404 VALUES LESS THAN (1365004800000)
,
PARTITION P_MEDIA20130405 VALUES LESS THAN (1365091200000)
,
PARTITION P_MEDIA20130406 VALUES LESS THAN (1365177600000)
,
PARTITION P_MEDIA20130407 VALUES LESS THAN (1365264000000)
,
PARTITION P_MEDIA20130408 VALUES LESS THAN (1365350400000)
,
PARTITION P_MEDIA20130409 VALUES LESS THAN (1365436800000)
,
PARTITION P_MEDIA20130410 VALUES LESS THAN (1365523200000)
,
PARTITION P_MEDIA20130411 VALUES LESS THAN (1365609600000)
,
PARTITION P_MEDIA20130412 VALUES LESS THAN (1365696000000)
,
PARTITION P_MEDIA20130413 VALUES LESS THAN (1365782400000)
,
PARTITION P_MEDIA20130414 VALUES LESS THAN (1365868800000)
,
PARTITION P_MEDIA20130415 VALUES LESS THAN (1365955200000)
,
PARTITION P_MEDIA20130416 VALUES LESS THAN (1366041600000)
,
PARTITION P_MEDIA20130417 VALUES LESS THAN (1366128000000)
,
PARTITION P_MEDIA20130418 VALUES LESS THAN (1366214400000)
,
PARTITION P_MEDIA20130419 VALUES LESS THAN (1366300800000)
,
PARTITION P_MEDIA20130420 VALUES LESS THAN (1366387200000)
,
PARTITION P_MEDIA20130421 VALUES LESS THAN (1366473600000)
,
PARTITION P_MEDIA20130422 VALUES LESS THAN (1366560000000)
,
PARTITION P_MEDIA20130423 VALUES LESS THAN (1366646400000)
,
PARTITION P_MEDIA20130424 VALUES LESS THAN (1366732800000)
,
PARTITION P_MEDIA20130425 VALUES LESS THAN (1366819200000)
,
PARTITION P_MEDIA20130426 VALUES LESS THAN (1366905600000)
,
PARTITION P_MEDIA20130427 VALUES LESS THAN (1366992000000)
,
PARTITION P_MEDIA20130428 VALUES LESS THAN (1367078400000)
,
PARTITION P_MEDIA20130429 VALUES LESS THAN (1367164800000)
,
PARTITION P_MEDIA20130430 VALUES LESS THAN (1367251200000)
,
PARTITION P_MEDIA20130501 VALUES LESS THAN (1367337600000)
,
PARTITION P_MEDIA20130502 VALUES LESS THAN (1367424000000)
,
PARTITION P_MEDIA20130503 VALUES LESS THAN (1367510400000)
,
PARTITION P_MEDIA20130504 VALUES LESS THAN (1367596800000)
,
PARTITION P_MEDIA20130505 VALUES LESS THAN (1367683200000)
,
PARTITION P_MEDIA20130506 VALUES LESS THAN (1367769600000)
,
PARTITION P_MEDIA20130507 VALUES LESS THAN (1367856000000)
,
PARTITION P_MEDIA20130508 VALUES LESS THAN (1367942400000)
,
PARTITION P_MEDIA20130509 VALUES LESS THAN (1368028800000)
,
PARTITION P_MEDIA20130510 VALUES LESS THAN (1368115200000)
,
PARTITION P_MEDIA20130511 VALUES LESS THAN (1368201600000)
,
PARTITION other VALUES LESS THAN (maxvalue)
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MEDIA_ID IS '多媒体编号，自增序列';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.VID IS '车辆ID';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.DEVICE_NO IS '手机码';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MTYPE_CODE IS '多媒体类型';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MFORMAT_CODE IS '多媒体格式';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.EVENT_TYPE IS '事件项编码 参见多媒体事件项编码表';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.UTC IS '多媒体上传时间UTC';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MEDIA_URI IS '多媒体URL';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.LENS_NO IS '通道号';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.FILE_SIZE IS '多媒体文件大小字节';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.DIMENSION IS '图片尺寸规格(1:320x240, 2:640x480, 3:800x600, 4:1024x768)';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.FILE_TYPE IS '文件类型 1:jpg;2:gif;3:tiff;4:其它';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.SAMPLE_RATE IS '音频采样频率(音频类多媒体信息需要)';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.LAT IS '纬度（单位：十万分之一度）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.LON IS '经度（单位：十万分之一度）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MAPLON IS '地图偏移后GPS经度';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MAPLAT IS '地图偏移后GPS纬度';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.ELEVATION IS '海拔高度（单位：米）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.DIRECTION IS '方向（单位：度）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.GPS_SPEED IS '速度(单位：米/小时)';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.STATUS_CODE IS '状态信息, 多值用逗号分隔';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.ALARM_CODE IS '报警信息，多值用逗号分隔';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.SYSUTC IS '入库时间utc';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.IS_OVERLOAD IS '是否超载(0 否 1 是)';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.EVENT_STATUS IS '事件状态（0 成功1 失败 2执行中）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.ENABLE_FLAG IS '有效标记 1:有效 0:无效 默认为1';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.SEQ IS 'SEQ指令唯一的标识码';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.SEND_USER IS '发送人ID';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.EVENTID IS '0：平台下发指令，1：定时动作，2：抢劫报警触发，3：碰撞侧翻报警触发，4：门开拍照，5：门关拍照，6：车门由开变关，时速从＜20公里超过20公里';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MEMO IS '备注';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.MULT_MEDIA_ID IS '多媒体数据ID（合规新加)';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.EVENT_TRIGGER_TIME IS '事件触发时间';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.READ_FLAG IS '已读标识（0－未读，1－已读）';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.OVERLOAD_NUM IS '超员人数';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.OVERLOAD_BY IS '标记超员人';

COMMENT ON COLUMN KCPT.TH_VEHICLE_MEDIA.OVERLOAD_TIME IS '标记超员时间';

CREATE UNIQUE INDEX KCPT.PK_VEHICLE_PICTURE2 ON KCPT.TH_VEHICLE_MEDIA
(MEDIA_ID)
LOGGING
TABLESPACE KCPT_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          179M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE KCPT.TEMP_MEDIA ADD (
  CONSTRAINT PK_VEHICLE_PICTURE2
  PRIMARY KEY
  (MEDIA_ID)
  USING INDEX KCPT.PK_VEHICLE_PICTURE2);



alter session force parallel dml;

alter session force parallel query;

--检查重定义的合理性
exec dbms_redefinition.can_redef_table('kcpt', 'TH_VEHICLE_MEDIA');
--重定义表
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'KCPT',
orig_table => 'TH_VEHICLE_MEDIA',
int_table => 'TEMP_MEDIA');
END;
/


--同步新表，这是可选的操作
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'kcpt',
orig_table => 'TH_VEHICLE_MEDIA',
int_table => 'TEMP_MEDIA');
END;
/


结束重定义
SQL> BEGIN
dbms_redefinition.finish_redef_table(uname => 'kcpt',orig_table => 'TH_VEHICLE_MEDIA',int_table => 'TEMP_MEDIA');
END;
/

删除临时表
SQL> DROP TABLE ALARM_EVENT purge;

--创建索引，在线重定义只重定义数据，索引还需要单独建立。

CREATE INDEX KCPT.MEDIAID_INDEX ON KCPT.TH_VEHICLE_MEDIA
(MULT_MEDIA_ID)
LOGGING
TABLESPACE KCPT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX KCPT.UTC_INDEX ON KCPT.TH_VEHICLE_MEDIA
(UTC, VID, EVENT_TYPE)
LOGGING
TABLESPACE KCPT_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          72M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX KCPT.VID_INDEX ON KCPT.TH_VEHICLE_MEDIA
(VID)
LOGGING
TABLESPACE KCPT_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          72M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



收集新表的统计信息
sql> exec dbms_stats.gather_table_stats('kcpt', 'ALARM_EVENT', cascade => true);






网站标题：在线重定义?普通表转换成分区表
转载来源：http://jkwzsj.com/article/gccsoe.html

其他资讯