在線重定義 ?普通表轉換成分區表
--收集表的統計信息
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);
