--收集表的統計資訊

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);

線上重定義 ?普通錶轉換成分割槽表

相關文章