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

2025-04-13 技术教程

--收集表的统计信息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_DATAPARTITION 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))NOLOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;

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)LOGGINGTABLESPACE KCPT_IDXPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( 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');--重定义表BEGINDBMS_REDEFINITION.start_redef_table(uname => 'KCPT',orig_table => 'TH_VEHICLE_MEDIA',int_table => 'TEMP_MEDIA');END;/

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

结束重定义SQL> BEGINdbms_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)LOGGINGTABLESPACE KCPTPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( 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)LOGGINGTABLESPACE KCPT_IDXPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( INITIAL 72M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )NOPARALLEL;

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

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