-- Create tablecreate table LIS.LCCONT( grpcontno CHAR(20) not null, contno CHAR(20) not null, PRoposalcontno CHAR(20) not null, prtno CHAR(20) not null, conttype CHAR(1), familytype CHAR(1), familyid CHAR(10), poltype CHAR(1), cardflag CHAR(1), managecom CHAR(10) not null, executecom CHAR(10), agentcom CHAR(20), agentcode CHAR(10) not null, agentgroup CHAR(12), agentcode1 CHAR(10), agenttype CHAR(20), salechnl CHAR(2), handler CHAR(10), passWord VARCHAR2(300), appntno CHAR(24), appntname VARCHAR2(200), appntsex CHAR(1), appntbirthday DATE, appntidtype CHAR(1), appntidno CHAR(20), insuredno CHAR(24) not null, insuredname VARCHAR2(200), insuredsex CHAR(1), insuredbirthday DATE, insuredidtype CHAR(1), insuredidno CHAR(20), payintv INTEGER, paymode CHAR(1), paylocation CHAR(1), disputedflag CHAR(1), outpayflag CHAR(1), getpolmode CHAR(1), signcom CHAR(10) not null, signdate DATE, signtime CHAR(8), consignno CHAR(20), bankcode CHAR(10), bankaccno VARCHAR2(40), accname VARCHAR2(60), printcount INTEGER, losttimes INTEGER, lang CHAR(1), currency CHAR(2), remark VARCHAR2(1600), peoples INTEGER, mult NUMBER(20,5), prem NUMBER(16,2), amnt NUMBER(16,2), sumprem NUMBER(16,2), dif NUMBER(12,2), paytodate DATE, firstpaydate DATE, cvalidate DATE, inputOperator CHAR(10), inputdate DATE, inputtime CHAR(8), approveflag CHAR(1), approvecode CHAR(10), approvedate DATE, approvetime CHAR(8), uwflag CHAR(1), uwoperator CHAR(10), uwdate DATE, uwtime CHAR(8), appflag CHAR(1) not null, polapplydate DATE, getpoldate DATE, getpoltime CHAR(8), customgetpoldate DATE, state CHAR(10), operator CHAR(10) not null, makedate DATE not null, maketime CHAR(8) not null, modifydate DATE not null, modifytime CHAR(8) not null, firsttrialoperator CHAR(10), firsttrialdate DATE, firsttrialtime CHAR(8), receiveoperator CHAR(10), receivedate DATE, receivetime CHAR(8), tempfeeno CHAR(20), selltype CHAR(2), forceuwflag CHAR(1), forceuwreason VARCHAR2(500), newbankcode CHAR(10), newbankaccno VARCHAR2(40), newaccname VARCHAR2(60), newpaymode CHAR(1), agentbankcode CHAR(6), bankagent VARCHAR2(20), firsttrialoperatorname VARCHAR2(120), agentconferno CHAR(20), inputtype VARCHAR2(20), rnewflag CHAR(2), policytype CHAR(1), custsignaturedate DATE, subinputtype VARCHAR2(2), salecom VARCHAR2(10), spop VARCHAR2(20), sptime_new TIMESTAMP(6), saleflag CHAR(1), salesmodel CHAR(1))--分區表，以MANAGECOM列為標準分區partition by range (MANAGECOM)( partition P_8600 values less than (8621)--當MANAGECOM小于8621時分到這個區 tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8621 values less than (8622)--當MANAGECOM小于8622時分到這個區 tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8622 values less than (8623) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8623 values less than (8624) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8624 values less than (8625) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8625 values less than (8626) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8626 values less than (8627) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8627 values less than (8628) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8628 values less than (8629) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8629 values less than (8631) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8631 values less than (8632) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8632 values less than (8633) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8633 values less than (8634) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8634 values less than (8635) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8635 values less than (8636) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8636 values less than (8637) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8637 values less than (8638) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8638 values less than (8639) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8639 values less than (8640) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8640 values less than (8641) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8641 values less than (8642) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8642 values less than (8643) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8643 values less than (8644) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8644 values less than (8645) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8645 values less than (8646) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8646 values less than (8647) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8647 values less than (8648) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8648 values less than (8649) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8649 values less than (8650) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8650 values less than (8651) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8651 values less than (8652) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8652 values less than (8653) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8653 values less than (8661) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8661 values less than (8673) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8673 values less than (8674) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_8674 values less than (8675) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ), partition P_OTHERCOM values less than (MAXVALUE) tablespace LIS pctfree 10 initrans 6 maxtrans 255 storage ( initial 80K next 1M minextents 1 maxextents unlimited ));-- Create/Recreate indexescreate index LIS.IDX_LCCONT on LIS.LCCONT (TRIM(PRTNO)) local;create index LIS.IDX_LCCONT_APPNTID on LIS.LCCONT (APPNTIDNO) local;create index LIS.IDX_LCCONT_DATE_COM on LIS.LCCONT (SIGNDATE, CVALIDATE, MODIFYDATE, MANAGECOM) local;create index LIS.IDX_LCCONT_DATE_COM2 on LIS.LCCONT (MAKEDATE, PAYTODATE, POLAPPLYDATE, MANAGECOM) local;create index LIS.IDX_LCCONT_SPTIME on LIS.LCCONT (SPTIME_NEW, 0) local;create index LIS.LCCONT_IDX_006 on LIS.LCCONT (TRIM(GRPCONTNO)) local;create index LIS.LCCONT_IDX_008 on LIS.LCCONT (TRIM(APPNTNO)) local;create index LIS.LCCONT_IDX_009 on LIS.LCCONT (TRIM(INSUREDNO)) local;create index LIS.LCCONT_IDX_013 on LIS.LCCONT (TRIM(PROPOSALCONTNO)) local;create index LIS.LCCONT_IDX_016 on LIS.LCCONT (TRIM(AGENTCODE)) local;create index LIS.LCCONT_IDX_017 on LIS.LCCONT (TRIM(AGENTGROUP)) local;create index LIS.LCCONT_IDX_018 on LIS.LCCONT (TRIM(AGENTCOM)) local;create index LIS.LCCONT_INDEX_1 on LIS.LCCONT (GRPCONTNO) local;create index LIS.LCCONT_INDEX_2 on LIS.LCCONT (PRTNO) local;create index LIS.LCCONT_INDEX_3 on LIS.LCCONT (PROPOSALCONTNO) local;create index LIS.LCCONT_INDEX_4 on LIS.LCCONT (AGENTCODE) local;create index LIS.LCCONT_INDEX_5 on LIS.LCCONT (AGENTGROUP) local;create index LIS.LCCONT_INDEX_6 on LIS.LCCONT (APPNTNO) local;create index LIS.LCCONT_INDEX_8 on LIS.LCCONT (INSUREDNO) local;-- Create/Recreate primary, unique and foreign key constraintsalter table LIS.LCCONT add constraint PK_LCCONT primary key (CONTNO, MANAGECOM) using index local;
新聞熱點
疑難解答
