select sum(bytes)/1024/1024 from dba_segments where segment_name='SMSPEC' select sum(bytes)/1024/1024 from dba_segments where segment_name in (select index_name from all_indexes where table_name='SMSPEC') CREATE TABLESPACE "SMSPECIDXTS" LOGGING DATAFILE 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS01.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS02.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS03.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS04.DBF' SIZE 1024M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "SMSPECIDXTS" LOGGING DATAFILE 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS01.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS02.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS03.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS04.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS05.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS06.DBF' SIZE 1024M REUSE, 'D:\ORACLE\ORADATA\DB\SMSPECIDXTS07.DBF' SIZE 1024M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; alter user supermag quota unlimited on smspects; alter user supermag quota unlimited on smspecidxts; create table smspec_p tablespace SMSPECTS partition by list (doctype) ( PARTITION P_AC VALUES ('AC'), PARTITION P_AD VALUES ('AD'), PARTITION P_BI VALUES ('BI'), PARTITION P_CA VALUES ('CA'), PARTITION P_CC VALUES ('CC'), PARTITION P_CI VALUES ('CI'), PARTITION P_CN VALUES ('CN'), PARTITION P_CO VALUES ('CO'), PARTITION P_CR VALUES ('CR'), PARTITION P_CS VALUES ('CS'), PARTITION P_EO VALUES ('EO'), PARTITION P_FA VALUES ('FA'), PARTITION P_GT VALUES ('GT'), PARTITION P_IL VALUES ('IL'), PARTITION P_IW VALUES ('IW'), PARTITION P_LA VALUES ('LA'), PARTITION P_MA VALUES ('MA'), PARTITION P_ME VALUES ('ME'), PARTITION P_OC VALUES ('OC'), PARTITION P_OR VALUES ('OR'), PARTITION P_PA VALUES ('PA'), PARTITION P_PD VALUES ('PD'), PARTITION P_PE VALUES ('PE'), PARTITION P_PL VALUES ('PL'), PARTITION P_PN VALUES ('PN'), PARTITION P_PO VALUES ('PO'), PARTITION P_RL VALUES ('RL'), PARTITION P_RO VALUES ('RO'), PARTITION P_RP VALUES ('RP'), PARTITION P_SL VALUES ('SL'), PARTITION P_SO VALUES ('SO'), PARTITION P_SR VALUES ('SR'), PARTITION P_WI VALUES ('WI'), PARTITION P_WO VALUES ('WO') ) as select * from smspec where 1>1; select replace(replace(upper(text),'SMSPEC', 'SMSPEC_P'),'TRIGGER', '/'||CHR(10)||'create or replace trigger') from sys.all_source s where s.type='TRIGGER' and s.name in (select t.trigger_name from all_triggers t where t.table_name='SMSPEC') order by name, line alter table SMSPEC_P add constraint SMCSPEC_PK_P primary key (DOCTYPE,DOCID,SPECITEM) using index tablespace SMSPECIDXTS; alter table SMSPEC_P add constraint SMCSPEC_DISPLAYPOS_P unique (DOCTYPE,DOCID,DISPLAYITEM) deferrable using index tablespace SMSPECIDXTS; alter table SMSPEC_P add constraint SMCSPECARTICLE_P foreign key (ARTICLE) references SMCARD (ARTICLE) disable; alter table SMSPEC_P add constraint SMCSPECCAUSE_P foreign key (CAUSETYPE,CAUSEID,CAUSESPECITEM) references SMSPEC_P (DOCTYPE,DOCID,SPECITEM) disable; alter table SMSPEC_P add constraint SMCSPECDOC_P foreign key (DOCTYPE,DOCID) references SMDOCUMENTS (DOCTYPE,ID) on delete cascade disable; -- Create/Recreate check constraints alter table SMSPEC_P add constraint SMCSPECBADCAUSE_P check (CauseType in ('WI','WO','IW','PO')); alter table SMSPEC_P add constraint SMCSPECCAUSEINCOMPLETE_P check (CauseType is null or (CauseID is not null and CauseSpecItem is not null)); alter table SMSPEC_P add constraint SMCSPECDOCTYPES_P check (DocType in ('AC','AD','BI','CA','CI','CC','CO','CN','CR','CS','FA','IL','IW','LA','MA','ME','OR','OC','PD','PE','PL','PM','PN','PO','PP','RL','RP','SO','SR','WI','WO')); alter table SMSPEC_P add constraint SMCSPECEXPQ_P check (ExpQuantity between 0 and Quantity); alter table SMSPEC_P add constraint SMCSPECITEMPRICE_P check (ItemPrice >= 0); alter table SMSPEC_P add constraint SMCSPECITEMPRICECUR_P check (ItemPriceCur >= 0); alter table SMSPEC_P add constraint SMCSPECITEMPRICENOTAX_P check (ItemPriceNoTax >= 0); alter table SMSPEC_P add constraint SMCSPECQUANTITY_P check (Quantity >= 0); alter table SMSPEC_P add constraint SMCSPECSELFCAUSE_P check (NOT (DocID=CauseID AND DocType=CauseType)); alter table SMSPEC_P add constraint SMCSPECSPECITEM_P check (SpecItem > 0); alter table SMSPEC_P add constraint SMCSPECTOTALPRICE_P check (TotalPrice >= 0); alter table SMSPEC_P add constraint SMCSPECTOTALPRICECUR_P check (TotalPriceCur >= 0); alter table SMSPEC_P add constraint SMCSPECTOTALPRICENOTAX_P check (TotalPriceNoTax >= 0); -- Create/Recreate indexes create index SMSPEC_ART_P on SMSPEC_P (ARTICLE,DOCTYPE,DOCID) tablespace SMSPECIDXTS; create index SMSPEC_CAUSEIDX_P on SMSPEC_P (CAUSETYPE,CAUSEID,CAUSESPECITEM) tablespace SMSPECIDXTS; select 'alter table '||c.table_name||' disable constraint '||c.constraint_name||';' from all_constraints c where c.table_name ='SMSPEC_P' and c.constraint_type='C' declare tmp varchar2(255); begin for m in (select 'alter table '||c.table_name||' add constraint '||c.constraint_name||'_P' ||' foreign key (#CLMN#) references SMSPEC_P (DOCTYPE,DOCID, SPECITEM) on delete cascade disable;' stmt, c.constraint_name cm, c.table_name tm from all_constraints c where c.r_constraint_name in (select c1.constraint_name from all_constraints c1 where c1.table_name='SMSPEC' and c1.constraint_type='P')) loop for n in (select ','||column_name f from sys.all_cons_columns c2 where c2.table_name=m.tm and c2.constraint_name=m.cm order by c2.constraint_name, position) loop tmp:=tmp||n.f; end loop; dbms_output.put_line(replace(m.stmt, '#CLMN#',substr(tmp, 2))); tmp:=''; end loop; end; alter table smspec_p disable constraint smcspec_pk_p keep index; alter table smspec_p disable constraint smcspec_displaypos_p keep index; grant select on SMSPEC_P to SUPERMAG_FN_ARTICLE_FREE_NDS; grant select on SMSPEC_P to SUPERMAG_FN_ATTACH_TAX; grant select on SMSPEC_P to SUPERMAG_FN_CARD_PRINTPRICER; grant select on SMSPEC_P to SUPERMAG_FN_CARD_VIEWDOCS; grant select on SMSPEC_P to SUPERMAG_FN_CARD_VIEWSUPPL; grant select on SMSPEC_P to SUPERMAG_FN_CASHSELLER; grant select on SMSPEC_P to SUPERMAG_FN_CH_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_CONTRACT_ARTICLE; grant select on SMSPEC_P to SUPERMAG_FN_CO_PRICE_CHANGE; grant select on SMSPEC_P to SUPERMAG_FN_DETAIL_PRICEEXTRA; grant select on SMSPEC_P to SUPERMAG_FN_DIFF_NDS_INCOME; grant select on SMSPEC_P to SUPERMAG_FN_DISCCARD; grant select on SMSPEC_P to SUPERMAG_FN_DISPARITY_GROUP; grant select, update, delete on SMSPEC_P to SUPERMAG_FN_DOC_AC_EDIT_DRAFT; grant select, update, delete on SMSPEC_P to SUPERMAG_FN_DOC_AC_EDIT_STORE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AC_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AC_MAKEAC; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AC_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AC_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AD_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_AD_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_BI_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_BI_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CA_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CA_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CA_SET_PRICES; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CC_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CI_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CN_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CN_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CS_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_CS_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_EO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_EO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_FA_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_FA_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_GT_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IL_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IL_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IL_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IW_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IW_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_IW_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_LA_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_LA_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_MA_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_MA_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_ME_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_ME_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_OC_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_OC_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_OR_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_OR_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PD_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PE_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PE_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PE_SET_PRICES; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PL_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PN_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_PO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RL_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RL_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RL_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_RP_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SL_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SL_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SR_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SR_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_SR_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WI_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WI_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WI_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WO_CREATE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WO_EXPORT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WO_PRINT; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WO_PRINTSIMPLE; grant select on SMSPEC_P to SUPERMAG_FN_DOC_WO_SET_PRICES; grant select on SMSPEC_P to SUPERMAG_FN_EFFECT_ACTION; grant select on SMSPEC_P to SUPERMAG_FN_EXEC_ORDER; grant select on SMSPEC_P to SUPERMAG_FN_EXPENSES_PROFIT; grant select on SMSPEC_P to SUPERMAG_FN_FIXED_PRICE; grant select on SMSPEC_P to SUPERMAG_FN_GOODS_NOT_SALE; grant select on SMSPEC_P to SUPERMAG_FN_INCOME_NOCERTIF; grant select on SMSPEC_P to SUPERMAG_FN_INCOMEPRICE_CHANGE; grant select on SMSPEC_P to SUPERMAG_FN_MISMATCH_PRICE; grant select on SMSPEC_P to SUPERMAG_FN_NSP; grant select on SMSPEC_P to SUPERMAG_FN_OBOROT; grant select on SMSPEC_P to SUPERMAG_FN_OPERFIFO; grant select on SMSPEC_P to SUPERMAG_FN_OSTATKI; grant select on SMSPEC_P to SUPERMAG_FN_OSTATKI_EXPIRY; grant select on SMSPEC_P to SUPERMAG_FN_OSTATKI_PROD; grant select on SMSPEC_P to SUPERMAG_FN_PACKING; grant select on SMSPEC_P to SUPERMAG_FN_PRICELIST; grant select on SMSPEC_P to SUPERMAG_FN_REALIZATION; grant select on SMSPEC_P to SUPERMAG_FN_REALWEIGHT; grant select on SMSPEC_P to SUPERMAG_FN_REASON_SALDOCHANGE; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_ACT_SORT; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_AUCTIONS; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_CASHDOCS; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_INVOICE; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_LOSTFIND; grant select on SMSPEC_P to SUPERMAG_FN_REESTRNACL; grant select on SMSPEC_P to SUPERMAG_FN_REESTRNACL_PROD; grant select on SMSPEC_P to SUPERMAG_FN_REESTRNACL_SPIS; grant select on SMSPEC_P to SUPERMAG_FN_REESTRNACL_SUPP; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_PAYORDERS; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_PRICECHANGE; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_SKL_TREB; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_TRANS; grant select on SMSPEC_P to SUPERMAG_FN_REESTR_ZAKAZ; grant select on SMSPEC_P to SUPERMAG_FN_REGISTRATION_CARD; grant select on SMSPEC_P to SUPERMAG_FN_REPADMIN_CALC; grant select on SMSPEC_P to SUPERMAG_FN_REPADMIN_PCLOSE; grant select on SMSPEC_P to SUPERMAG_FN_SALEBOOK; grant select on SMSPEC_P to SUPERMAG_FN_SPISANIE; grant select on SMSPEC_P to SUPERMAG_FN_SPISANIE_LOSS; grant select on SMSPEC_P to SUPERMAG_FN_TOVREP_FORM29; grant select on SMSPEC_P to SUPERMAG_FN_UNACCEPTED_TRANS; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1701; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1702; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1703; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1704; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1705; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1706; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1707; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1708; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1709; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1721; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1730; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1731; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1732; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1737; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1738; grant select on SMSPEC_P to SUPERMAG_FN_USR_REP1742; grant select on SMSPEC_P to SUPERMAG_FN_WEIGHT_CONSIGNMENT; grant select on SMSPEC_P to SUPERMAG_FN_WITHOUT_ZAKAZ; grant select on SMSPEC_P to SUPERMAG_FN_ZAKAZ; grant select on SMSPEC_P to SUPERMAG_MODULE_AUCTIONS; grant select on SMSPEC_P to SUPERMAG_MODULE_CLIENTS; grant select on SMSPEC_P to SUPERMAG_MODULE_DEPOT; grant select on SMSPEC_P to SUPERMAG_MODULE_DISC; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_AC; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_IL; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_IW; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_MA; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_OR; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_RL; grant select on SMSPEC_P to SUPERMAG_MODULE_DOC_WI; grant select on SMSPEC_P to SUPERMAG_MODULE_GOODS; alter table supermag.smspec parallel 4; begin DBMS_REDEFINITION.START_REDEF_TABLE('SUPERMAG','SMSPEC','SMSPEC_P'); end; / begin DBMS_REDEFINITION.FINISH_REDEF_TABLE('SUPERMAG','SMSPEC','SMSPEC_P'); end; / select 'alter table '||c.table_name||' drop constraint '||c.constraint_name||';' stmt, c.constraint_name cm, c.table_name tm from all_constraints c where c.r_constraint_name in (select c1.constraint_name from all_constraints c1 where c1.table_name='SMSPEC_P' and c1.constraint_type='P') drop table smspec_p; select 'alter table '||c.table_name||' rename constraint '||c.constraint_name||' to '|| replace(c.constraint_name, '_P','')||';' stmt, c.constraint_name cm, c.table_name tm from all_constraints c where c.r_constraint_name in (select c1.constraint_name from all_constraints c1 where c1.table_name='SMSPEC' and c1.constraint_type='P') alter table smspec rename constraint smcspec_pk_p to smcspec_pk; alter table smspec rename constraint smcspecdoc_p to smcspecdoc; alter table smspec rename constraint smcspecarticle_P to smcspecarticle; alter index smcspec_displaypos_p rename to smcspec_displaypos; alter index smcspec_pk_p rename to smcspec_pk; alter index smspec_art_p rename to smspec_art; alter index smspec_causeidx_P rename to smspec_causeidx; compile invalid begin dbms_stats.gather_table_stats('SUPERMAG','SMSPEC'); end; create iNDEX smspec_p_tp on smspec(doctype) local;