--收集表的统计信息
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://cdkjz.cn/article/gccsoe.html
