--Create table
CREATE TABLE Tt_fvp_ocr_address
(
ID number is not NULL,
Waybill_no VARCHAR2 (+) NOT NULL,
Dest_zone_code VARCHAR2 (32),
Confidence number (16,4),
Input_tm DATE,
INSERT_TM DATE default sysdate NOT NULL,
DEAL_FLG Number (2) default 0 NOT NULL,
Deal_count Number (2) default 0 NOT NULL,
Deal_ip VARCHAR2 (30),
Deal_tm DATE,
Ocr_addr VARCHAR2 (1000)
)
Partition by range (INSERT_TM)
(
Partition tt_fvp_ocr_address_p20170616 values less than (TIMESTAMP ' 2017-06-17 00:00:00 '),
Partition tt_fvp_ocr_address_p20170617 values less than (TIMESTAMP ' 2017-06-18 00:00:00 '),
Partition tt_fvp_ocr_address_p20170618 values less than (TIMESTAMP ' 2017-06-19 00:00:00 '),
Partition tt_fvp_ocr_address_p20170619 values less than (TIMESTAMP ' 2017-06-20 00:00:00 '),
Partition tt_fvp_ocr_address_p20170620 values less than (TIMESTAMP ' 2017-06-21 00:00:00 '),
Partition tt_fvp_ocr_address_p20170621 values less than (TIMESTAMP ' 2017-06-22 00:00:00 '),
Partition tt_fvp_ocr_address_p20170622 values less than (TIMESTAMP ' 2017-06-23 00:00:00 '),
Partition tt_fvp_ocr_address_p20170623 values less than (TIMESTAMP ' 2017-06-24 00:00:00 '),
Partition tt_fvp_ocr_address_p20170624 values less than (TIMESTAMP ' 2017-06-25 00:00:00 '),
Partition tt_fvp_ocr_address_p20170625 values less than (TIMESTAMP ' 2017-06-26 00:00:00 '),
Partition tt_fvp_ocr_address_p20170626 values less than (TIMESTAMP ' 2017-06-27 00:00:00 ')
);
--ADD comments to the table
Comment on table tt_fvp_ocr_address
is ' OCR Address table ';
--ADD comments to the columns
Comment on column tt_fvp_ocr_address.id
is ' ID ';
Comment on column tt_fvp_ocr_address.waybill_no
Is ' waybill number ';
Comment on column Tt_fvp_ocr_address.dest_zone_code
Is ' receiver pays city code ';
Comment on column tt_fvp_ocr_address.confidence
is ' credibility ';
Comment on column Tt_fvp_ocr_address.input_tm
Is ' record time ';
Comment on column Tt_fvp_ocr_address.insert_tm
Is ' insertion time ';
Comment on column TT_FVP_OCR_ADDRESS.DEAL_FLG
is ' processing mark ';
Comment on column Tt_fvp_ocr_address.deal_count
is ' processing times ';
Comment on column tt_fvp_ocr_address.deal_ip
is ' processing IP ';
Comment on column Tt_fvp_ocr_address.deal_tm
Is ' processing time ';
Comment on column tt_fvp_ocr_address.ocr_addr
Is ' Error correction address ';
Create INDEX idx_tt_fvp_ocr_address on tt_fvp_ocr_address (DEAL_FLG)
Local
--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE tt_fvp_ocr_address
Add constraint pk_tt_fvp_ocr_address primary key (ID, INSERT_TM)
Using index
Local
--Create a sequence of fvp_ocr_address tables
Create sequence Seq_tt_fvp_ocr_address
MinValue 1
MaxValue 999999999999999999999
Start with 1000
Increment by 1
Cache 20;
--Maintenance partition
DECLARE
V_out VARCHAR2 (2000);
BEGIN
Dbamon. Config_tab_policy (v_out, ' SSS ', ' tt_fvp_ocr_address ', 7,1,0,24, ' Day ');
Dbms_output. Put_Line (v_out);
END;
Examples of SQL partition representations