Last active
December 4, 2020 14:54
-
-
Save CedricL46/05199f9b1360db0c354ef381ee3969c2 to your computer and use it in GitHub Desktop.
Read full tutorial here : https://cedricleruth.com/how-to-partition-for-performance-your-oracle-soa-bpel-database/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -------------------------------------------------------- | |
| -- MIT License | |
| -- Author Cedric Leruth | |
| -- | |
| --Permission is hereby granted, free of charge, to any person obtaining a copy | |
| --of this software and associated documentation files (the "Software"), to deal | |
| --in the Software without restriction, including without limitation the rights | |
| --to use, copy, modify, merge, publish, distribute, sublicense, and/or sell | |
| --copies of the Software, and to permit persons to whom the Software is | |
| --furnished to do so, subject to the following conditions: | |
| -- | |
| --The above copyright notice and this permission notice shall be included in all | |
| --copies or substantial portions of the Software. | |
| -- | |
| --THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR | |
| --IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, | |
| --FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE | |
| --AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER | |
| --LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, | |
| --OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE | |
| --SOFTWARE. | |
| -------------------------------------------------------- | |
| -------------------------------------------------------- | |
| -- NOTE : Do not use this directly in your production environnement. | |
| -- Read it, test it, and update it to fit your specific instances | |
| -- Replace YOUR_SOA_USER with your own schema name | |
| -- Replace YOUR_USER_TABLESPACE with your own tablespace name | |
| -------------------------------------------------------- | |
| WHENEVER SQLERROR EXIT | |
| alter session set current_schema=YOUR_SOA_USER; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table COMPOSITE_INSTANCE | |
| -------------------------------------------------------- | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ECID"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ID"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CIDN"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CO_ID"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_STATE"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CREATED"; | |
| ALTER TABLE COMPOSITE_INSTANCE RENAME TO COMPOSITE_INSTANCE_SAV; | |
| CREATE TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE" | |
| ( "ECID" VARCHAR2(100 BYTE), | |
| "ID" NUMBER(*,0), | |
| "PARENT_ID" VARCHAR2(100 BYTE), | |
| "CONVERSATION_ID" VARCHAR2(100 BYTE), | |
| "COMPOSITE_DN" VARCHAR2(500 BYTE), | |
| "SOURCE_NAME" VARCHAR2(100 BYTE), | |
| "SOURCE_TYPE" VARCHAR2(200 BYTE), | |
| "SOURCE_ACTION_TYPE" VARCHAR2(10 BYTE), | |
| "SOURCE_ACTION_NAME" VARCHAR2(500 BYTE), | |
| "BATCH_ID" VARCHAR2(100 BYTE), | |
| "BATCH_INDEX" NUMBER(*,0), | |
| "BUSINESS_STATUS" NVARCHAR2(100), | |
| "INDEX1" VARCHAR2(100 BYTE), | |
| "INDEX2" VARCHAR2(100 BYTE), | |
| "INDEX3" VARCHAR2(100 BYTE), | |
| "INDEX4" VARCHAR2(100 BYTE), | |
| "INDEX5" VARCHAR2(100 BYTE), | |
| "INDEX6" VARCHAR2(100 BYTE), | |
| "TITLE" NVARCHAR2(100), | |
| "TAGS" VARCHAR2(2000 BYTE), | |
| "TEST_RUN_NAME" VARCHAR2(100 BYTE), | |
| "TEST_RUN_ID" VARCHAR2(100 BYTE), | |
| "TEST_SUITE" VARCHAR2(100 BYTE), | |
| "TEST_CASE" VARCHAR2(100 BYTE), | |
| "STATE" NUMBER(3,0), | |
| "LIVE_INSTANCES" NUMBER(*,0), | |
| "STATE_COUNT" NUMBER, | |
| "HAS_ASSOC" CHAR(1 BYTE), | |
| "VERSION" NUMBER(*,0), | |
| "PARTITION_DATE" TIMESTAMP (6) DEFAULT systimestamp, | |
| "TENANT_ID" NUMBER(18,0) DEFAULT -1, | |
| "CREATED_BY" VARCHAR2(100 BYTE), | |
| "CREATED_TIME" TIMESTAMP (6), | |
| "UPDATED_BY" VARCHAR2(100 BYTE), | |
| "UPDATED_TIME" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE" MODIFY ("CREATED_TIME" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE_SAV order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.COMPOSITE_INSTANCE SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."COMPOSITE_INSTANCE" select /*+ PARALLEL(8) */ * from "YOUR_SOA_USER"."COMPOSITE_INSTANCE_SAV"; | |
| COMMIT; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ECID" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("ECID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ID" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CIDN" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("COMPOSITE_DN", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CO_ID" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("CONVERSATION_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_STATE" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_CREATED" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING Table AUDIT_TRAIL | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" DROP CONSTRAINT "AT_PK"; | |
| ALTER TABLE AUDIT_TRAIL RENAME TO AUDIT_TRAIL_SAV; | |
| CREATE TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" | |
| ( "CIKEY" NUMBER(*,0), | |
| "COUNT_ID" NUMBER(*,0), | |
| "BLOCK" NUMBER(*,0), | |
| "BLOCK_CSIZE" NUMBER(*,0), | |
| "BLOCK_USIZE" NUMBER(*,0), | |
| "LOG" RAW(2000), | |
| "CI_PARTITION_DATE" TIMESTAMP (6), | |
| "NUM_OF_EVENTS" NUMBER(*,0) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (CI_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LES THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" MODIFY ("CI_PARTITION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" MODIFY ("COUNT_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" MODIFY ("CIKEY" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.AUDIT_TRAIL SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."AUDIT_TRAIL" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."AUDIT_TRAIL_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."AT_PK" ON "YOUR_SOA_USER"."AUDIT_TRAIL" ("CIKEY", "COUNT_ID") | |
| GLOBAL PARTITION BY HASH (CIKEY) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."AUDIT_TRAIL" ADD CONSTRAINT "AT_PK" PRIMARY KEY ("CIKEY", "COUNT_ID") USING INDEX; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table XML_DOCUMENT | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."XML_DOCUMENT" DROP CONSTRAINT "DOC_STORE_PK"; | |
| ALTER TABLE XML_DOCUMENT RENAME TO XML_DOCUMENT_SAV; | |
| CREATE TABLE "YOUR_SOA_USER"."XML_DOCUMENT" | |
| ( "DOCUMENT_ID" VARCHAR2(200 BYTE), | |
| "DOCUMENT" BLOB, | |
| "DOCUMENT_BINARY_FORMAT" NUMBER(*,0), | |
| "DOCUMENT_TYPE" NUMBER(*,0), | |
| "DOC_PARTITION_DATE" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 1 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| TABLESPACE "YOUR_USER_TABLESPACE" | |
| LOB ("DOCUMENT") STORE AS SECUREFILE ( | |
| TABLESPACE "YOUR_USER_TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192 | |
| CACHE NOCOMPRESS KEEP_DUPLICATES | |
| STORAGE(INITIAL 106496 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) | |
| PARTITION BY RANGE (DOC_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LES THAN (MAXVALUE) | |
| ) | |
| ; | |
| ALTER TABLE "YOUR_SOA_USER"."XML_DOCUMENT" MODIFY ("DOCUMENT_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."XML_DOCUMENT" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.XML_DOCUMENT SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."XML_DOCUMENT" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."XML_DOCUMENT_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."DOC_STORE_PK" ON "YOUR_SOA_USER"."XML_DOCUMENT" ("DOCUMENT_ID") REVERSE | |
| GLOBAL PARTITION BY HASH (DOCUMENT_ID) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."XML_DOCUMENT" ADD CONSTRAINT "DOC_STORE_PK" PRIMARY KEY ("DOCUMENT_ID") USING INDEX REVERSE ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table HEADERS_PROPERTIES | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" DROP CONSTRAINT "HEADERS_PROPERTIES_PK"; | |
| ALTER TABLE HEADERS_PROPERTIES RENAME TO HEADERS_PROPERTIES_SAV; | |
| CREATE TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" | |
| ( "MESSAGE_GUID" VARCHAR2(200 BYTE), | |
| "COUNT_ID" NUMBER(*,0), | |
| "BIN_CSIZE" NUMBER(*,0), | |
| "BIN_USIZE" NUMBER(*,0), | |
| "BIN" RAW(2000), | |
| "MODIFY_DATE" TIMESTAMP (6), | |
| "DLV_PARTITION_DATE" TIMESTAMP (6) DEFAULT systimestamp | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (DLV_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LES THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" MODIFY ("COUNT_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" MODIFY ("MESSAGE_GUID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.HEADERS_PROPERTIES SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."HEADERS_PROPERTIES" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."HEADERS_PROPERTIES_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."HEADERS_PROPERTIES_PK" ON "YOUR_SOA_USER"."HEADERS_PROPERTIES" ("MESSAGE_GUID", "COUNT_ID") | |
| GLOBAL PARTITION BY HASH (MESSAGE_GUID) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."HEADERS_PROPERTIES" ADD CONSTRAINT "HEADERS_PROPERTIES_PK" PRIMARY KEY ("MESSAGE_GUID", "COUNT_ID") USING INDEX ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table REFERENCE_INSTANCE | |
| -------------------------------------------------------- | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_ECID"; | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_ID"; | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_CO_ID"; | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_CDN_STATE"; | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_STATE"; | |
| DROP INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_TIME_CDN"; | |
| ALTER TABLE "REFERENCE_INSTANCE" RENAME TO "REFERENCE_INSTANCE_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."REFERENCE_INSTANCE" | |
| ( "ECID" VARCHAR2(100 BYTE), | |
| "ID" NUMBER(*,0), | |
| "COMPOSITE_INSTANCE_ID" NUMBER(*,0), | |
| "PARENT_ID" VARCHAR2(100 BYTE), | |
| "CONVERSATION_ID" VARCHAR2(100 BYTE), | |
| "COMPOSITE_DN" VARCHAR2(500 BYTE), | |
| "PROTOCOL_CORRELATION_ID" VARCHAR2(100 BYTE), | |
| "REFERENCE_NAME" VARCHAR2(200 BYTE), | |
| "BINDING_TYPE" VARCHAR2(200 BYTE), | |
| "OPERATION_NAME" VARCHAR2(500 BYTE), | |
| "STATE" NUMBER(*,0), | |
| "ADDITIONAL_PROPERTIES" VARCHAR2(4000 BYTE), | |
| "ERROR_CODE" VARCHAR2(100 BYTE), | |
| "ERROR_MESSAGE" CLOB, | |
| "STACK_TRACE" CLOB, | |
| "CPST_PARTITION_DATE" TIMESTAMP (6), | |
| "TENANT_ID" NUMBER(18,0) DEFAULT -1, | |
| "CREATED_BY" VARCHAR2(100 BYTE), | |
| "CREATED_TIME" TIMESTAMP (6), | |
| "UPDATED_BY" VARCHAR2(100 BYTE), | |
| "UPDATED_TIME" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| TABLESPACE "YOUR_USER_TABLESPACE" | |
| LOB ("ERROR_MESSAGE") STORE AS SECUREFILE ( | |
| TABLESPACE "YOUR_USER_TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192 | |
| CACHE NOCOMPRESS KEEP_DUPLICATES | |
| STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) | |
| LOB ("STACK_TRACE") STORE AS SECUREFILE ( | |
| TABLESPACE "YOUR_USER_TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192 | |
| CACHE NOCOMPRESS KEEP_DUPLICATES | |
| STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) | |
| PARTITION BY RANGE (CPST_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| ; | |
| ALTER TABLE "YOUR_SOA_USER"."REFERENCE_INSTANCE" MODIFY ("CREATED_TIME" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."REFERENCE_INSTANCE" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.REFERENCE_INSTANCE SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."REFERENCE_INSTANCE" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."REFERENCE_INSTANCE_SAV"; | |
| COMMIT; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_ECID" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("ECID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_ID" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_CO_ID" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("PROTOCOL_CORRELATION_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_CDN_STATE" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("COMPOSITE_DN", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_STATE" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."REFERENCE_INSTANCE_TIME_CDN" ON "YOUR_SOA_USER"."REFERENCE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table DLV_SUBSCRIPTION | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" DROP CONSTRAINT "DLV_SUBSCRIPTION_PK"; | |
| DROP INDEX "YOUR_SOA_USER"."DS_FK"; | |
| DROP INDEX "YOUR_SOA_USER"."DS_CONVERSATION"; | |
| DROP INDEX "YOUR_SOA_USER"."DS_CONV_STATE"; | |
| ALTER TABLE "DLV_SUBSCRIPTION" RENAME TO "DLV_SUBSCRIPTION_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" | |
| ( "CONV_ID" VARCHAR2(256 BYTE), | |
| "CONV_TYPE" NUMBER(*,0), | |
| "CIKEY" NUMBER(*,0), | |
| "PARTNER_LINK" VARCHAR2(256 BYTE), | |
| "PROCESS_GUID" VARCHAR2(50 BYTE), | |
| "OPERATION_NAME" VARCHAR2(128 BYTE), | |
| "EVENT_NAME" VARCHAR2(594 BYTE), | |
| "SUBSCRIBER_ID" VARCHAR2(1024 BYTE), | |
| "SERVICE_NAME" VARCHAR2(128 BYTE), | |
| "SUBSCRIPTION_DATE" TIMESTAMP (6), | |
| "STATE" NUMBER(*,0) DEFAULT 0, | |
| "PROPERTIES" VARCHAR2(2000 BYTE), | |
| "EXT_STRING1" VARCHAR2(100 BYTE), | |
| "EXT_STRING2" VARCHAR2(100 BYTE), | |
| "EXT_INT1" NUMBER(*,0), | |
| "VERSION" NUMBER(*,0), | |
| "CACHE_VERSION" NUMBER(*,0), | |
| "COMPOSITE_NAME" VARCHAR2(500 BYTE), | |
| "DOMAIN_NAME" VARCHAR2(50 BYTE), | |
| "COMPONENT_NAME" VARCHAR2(300 BYTE), | |
| "COMPOSITE_LABEL" VARCHAR2(50 BYTE), | |
| "COMPOSITE_REVISION" VARCHAR2(50 BYTE), | |
| "CI_PARTITION_DATE" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (CI_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" MODIFY ("CI_PARTITION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" MODIFY ("STATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" MODIFY ("SUBSCRIBER_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.DLV_SUBSCRIPTION SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."DLV_SUBSCRIPTION" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."DLV_SUBSCRIPTION_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."DLV_SUBSCRIPTION_PK" ON "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ("SUBSCRIBER_ID") | |
| GLOBAL PARTITION BY HASH(SUBSCRIBER_ID) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DS_FK" ON "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ("CIKEY") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DS_CONVERSATION" ON "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ("CONV_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DS_CONV_STATE" ON "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ("CONV_ID", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_SUBSCRIPTION" ADD CONSTRAINT "DLV_SUBSCRIPTION_PK" PRIMARY KEY ("SUBSCRIBER_ID") USING INDEX; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table DLV_MESSAGE | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" DROP CONSTRAINT "DLV_MESSAGE_PK"; | |
| DROP INDEX "YOUR_SOA_USER"."DM_CONVERSATION"; | |
| DROP INDEX "YOUR_SOA_USER"."DM_RECEIVE_DATE"; | |
| DROP INDEX "YOUR_SOA_USER"."DLV_MESSAGE_CIKEY"; | |
| DROP INDEX "YOUR_SOA_USER"."DLV_MESSAGE_DATE_ECID"; | |
| ALTER TABLE "DLV_MESSAGE" RENAME TO "DLV_MESSAGE_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."DLV_MESSAGE" | |
| ( "CONV_ID" VARCHAR2(256 BYTE), | |
| "CONV_TYPE" NUMBER(*,0), | |
| "MESSAGE_GUID" VARCHAR2(50 BYTE), | |
| "PARTNER_LINK" VARCHAR2(256 BYTE), | |
| "OPERATION_NAME" VARCHAR2(128 BYTE), | |
| "EVENT_NAME" VARCHAR2(594 BYTE), | |
| "RECEIVE_DATE" TIMESTAMP (6), | |
| "STATE" NUMBER(*,0) DEFAULT 0, | |
| "RES_SUBSCRIBER" VARCHAR2(1024 BYTE), | |
| "EXT_STRING1" VARCHAR2(100 BYTE), | |
| "EXT_STRING2" VARCHAR2(100 BYTE), | |
| "EXT_INT1" NUMBER(*,0), | |
| "DLV_TYPE" NUMBER(*,0), | |
| "MASTER_CONV_ID" VARCHAR2(256 BYTE), | |
| "PRIORITY" NUMBER(*,0), | |
| "COMPOSITE_NAME" VARCHAR2(500 BYTE), | |
| "DOMAIN_NAME" VARCHAR2(50 BYTE), | |
| "COMPONENT_NAME" VARCHAR2(300 BYTE), | |
| "COMPOSITE_LABEL" VARCHAR2(50 BYTE), | |
| "COMPOSITE_REVISION" VARCHAR2(50 BYTE), | |
| "COMPONENT_TYPE" VARCHAR2(10 BYTE), | |
| "CIKEY" NUMBER(*,0), | |
| "RECOVER_COUNT" NUMBER(*,0), | |
| "HEADER_PROPERTIES_BIN_FORMAT" NUMBER(*,0), | |
| "ECID" VARCHAR2(100 BYTE), | |
| "CLUSTER_NODE_ID" VARCHAR2(100 BYTE), | |
| "CLUSTER_NODE_KEY" NUMBER(*,0) DEFAULT -1, | |
| "TENANT_ID" NUMBER(18,0) DEFAULT -1, | |
| "CACHE_VERSION" NUMBER(*,0), | |
| "PROPERTIES" VARCHAR2(2000 BYTE), | |
| "HEADERS_REF_ID" VARCHAR2(100 BYTE) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (RECEIVE_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" MODIFY ("COMPONENT_TYPE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" MODIFY ("STATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" MODIFY ("MESSAGE_GUID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.DLV_MESSAGE SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."DLV_MESSAGE" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."DLV_MESSAGE_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."DLV_MESSAGE_PK" ON "YOUR_SOA_USER"."DLV_MESSAGE" ("MESSAGE_GUID") | |
| GLOBAL PARTITION BY HASH(MESSAGE_GUID) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DM_CONVERSATION" ON "YOUR_SOA_USER"."DLV_MESSAGE" ("CONV_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DM_RECEIVE_DATE" ON "YOUR_SOA_USER"."DLV_MESSAGE" ("RECEIVE_DATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DLV_MESSAGE_CIKEY" ON "YOUR_SOA_USER"."DLV_MESSAGE" ("CIKEY") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DLV_MESSAGE_DATE_ECID" ON "YOUR_SOA_USER"."DLV_MESSAGE" ("ECID", "RECEIVE_DATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| ALTER TABLE "YOUR_SOA_USER"."DLV_MESSAGE" ADD CONSTRAINT "DLV_MESSAGE_PK" PRIMARY KEY ("MESSAGE_GUID") USING INDEX; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table WORK_ITEM | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" DROP CONSTRAINT WORK_ITEM_PK; | |
| DROP INDEX "YOUR_SOA_USER"."WI_EXPIRED"; | |
| DROP INDEX "YOUR_SOA_USER"."WI_STATE_KEY"; | |
| ALTER TABLE "WORK_ITEM" RENAME TO "WORK_ITEM_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."WORK_ITEM" | |
| ( "CIKEY" NUMBER(*,0), | |
| "NODE_ID" VARCHAR2(200 BYTE), | |
| "SCOPE_ID" VARCHAR2(1024 BYTE), | |
| "COUNT_ID" NUMBER(*,0), | |
| "CREATION_DATE" TIMESTAMP (6), | |
| "CREATOR" VARCHAR2(256 BYTE), | |
| "MODIFY_DATE" TIMESTAMP (6), | |
| "MODIFIER" VARCHAR2(100 BYTE), | |
| "STATE" NUMBER(*,0), | |
| "TRANSITION" NUMBER(*,0), | |
| "EXCEPTION" NUMBER(*,0) DEFAULT 0, | |
| "EXP_DATE" TIMESTAMP (6), | |
| "EXP_FLAG" NUMBER(*,0) DEFAULT 0, | |
| "PRIORITY" NUMBER(*,0), | |
| "LABEL" NVARCHAR2(128), | |
| "CUSTOM_ID" VARCHAR2(256 BYTE), | |
| "COMMENTS" NVARCHAR2(2000), | |
| "REFERENCE_ID" VARCHAR2(128 BYTE), | |
| "IDEMPOTENT_FLAG" NUMBER(*,0) DEFAULT 0, | |
| "EXECUTION_TYPE" NUMBER(*,0) DEFAULT 0, | |
| "FIRST_DELAY" NUMBER(*,0), | |
| "DELAY" NUMBER(*,0), | |
| "EXT_STRING1" VARCHAR2(100 BYTE), | |
| "EXT_STRING2" VARCHAR2(100 BYTE), | |
| "EXT_INT1" NUMBER(*,0), | |
| "CLUSTER_NODE_ID" VARCHAR2(100 BYTE), | |
| "VERSION" NUMBER(*,0), | |
| "CI_PARTITION_DATE" TIMESTAMP (6), | |
| "CLUSTER_NODE_KEY" NUMBER(*,0) DEFAULT -1 | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (CI_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("CI_PARTITION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("EXECUTION_TYPE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("IDEMPOTENT_FLAG" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("EXP_FLAG" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("EXCEPTION" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("COUNT_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("SCOPE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("NODE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" MODIFY ("CIKEY" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.WORK_ITEM SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."WORK_ITEM" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."WORK_ITEM_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."WORK_ITEM_PK" ON "YOUR_SOA_USER"."WORK_ITEM" ("CIKEY", "NODE_ID", "SCOPE_ID", "COUNT_ID") | |
| GLOBAL PARTITION BY HASH(CIKEY) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."WI_EXPIRED" ON "YOUR_SOA_USER"."WORK_ITEM" ("EXP_DATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| CREATE INDEX "YOUR_SOA_USER"."WI_STATE_KEY" ON "YOUR_SOA_USER"."WORK_ITEM" ("STATE", "CIKEY") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_SOA_USER" ; | |
| ALTER TABLE "YOUR_SOA_USER"."WORK_ITEM" ADD CONSTRAINT "WORK_ITEM_PK" PRIMARY KEY ("CIKEY", "NODE_ID", "SCOPE_ID", "COUNT_ID") USING INDEX; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table DOCUMENT_DLV_MSG_REF | |
| -------------------------------------------------------- | |
| DROP INDEX "YOUR_SOA_USER"."DOC_DLV_MSG_GUID_INDEX"; | |
| DROP INDEX "YOUR_SOA_USER"."DOC_DLV_MSG_ID_INDEX"; | |
| ALTER TABLE "DOCUMENT_DLV_MSG_REF" RENAME TO "DOCUMENT_DLV_MSG_REF_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" | |
| ( "MESSAGE_GUID" VARCHAR2(50 BYTE), | |
| "DOCUMENT_ID" VARCHAR2(200 BYTE), | |
| "PART_NAME" VARCHAR2(100 BYTE), | |
| "DOCUMENT_TYPE" NUMBER(*,0), | |
| "DLV_PARTITION_DATE" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (DLV_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" MODIFY ("DLV_PARTITION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.DOCUMENT_DLV_MSG_REF SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF_SAV"; | |
| COMMIT; | |
| CREATE INDEX "YOUR_SOA_USER"."DOC_DLV_MSG_GUID_INDEX" ON "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" ("MESSAGE_GUID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."DOC_DLV_MSG_ID_INDEX" ON "YOUR_SOA_USER"."DOCUMENT_DLV_MSG_REF" ("DOCUMENT_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table CUBE_INSTANCE | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" DROP CONSTRAINT "CI_PK"; | |
| DROP INDEX "YOUR_SOA_USER"."CI_ECID"; | |
| DROP INDEX "YOUR_SOA_USER"."CI_CUSTOM3"; | |
| DROP INDEX "YOUR_SOA_USER"."CI_CREATION_DATE"; | |
| DROP INDEX "YOUR_SOA_USER"."CI_NAME_REV_STATE"; | |
| ALTER TABLE "CUBE_INSTANCE" RENAME TO "CUBE_INSTANCE_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" | |
| ( "CIKEY" NUMBER(*,0), | |
| "CREATION_DATE" TIMESTAMP (6), | |
| "CREATOR" VARCHAR2(100 BYTE), | |
| "MODIFY_DATE" TIMESTAMP (6), | |
| "MODIFIER" VARCHAR2(100 BYTE), | |
| "STATE" NUMBER(*,0), | |
| "PRIORITY" NUMBER(*,0), | |
| "TITLE" NVARCHAR2(200), | |
| "STATUS" NVARCHAR2(100), | |
| "STAGE" VARCHAR2(100 BYTE), | |
| "CONVERSATION_ID" VARCHAR2(256 BYTE), | |
| "ROOT_ID" VARCHAR2(100 BYTE), | |
| "PARENT_ID" VARCHAR2(100 BYTE), | |
| "SCOPE_REVISION" NUMBER(*,0), | |
| "SCOPE_CSIZE" NUMBER(*,0), | |
| "SCOPE_USIZE" NUMBER(*,0), | |
| "PROCESS_TYPE" NUMBER(*,0), | |
| "METADATA" NVARCHAR2(1000), | |
| "EXT_STRING1" VARCHAR2(100 BYTE), | |
| "EXT_STRING2" VARCHAR2(100 BYTE), | |
| "EXT_INT1" NUMBER(*,0), | |
| "TEST_RUN_ID" VARCHAR2(100 BYTE), | |
| "TEST_RUN_NAME" VARCHAR2(100 BYTE), | |
| "TEST_CASE" VARCHAR2(100 BYTE), | |
| "TEST_SUITE" VARCHAR2(100 BYTE), | |
| "ECID" VARCHAR2(100 BYTE), | |
| "CMPST_ID" VARCHAR2(100 BYTE), | |
| "OUTCOME" VARCHAR2(100 BYTE), | |
| "TRACKING_LEVEL" VARCHAR2(16 BYTE), | |
| "AT_COUNT_ID" NUMBER(*,0), | |
| "AT_EVENT_ID" NUMBER(*,0), | |
| "AT_DETAIL_ID" NUMBER(*,0), | |
| "VERSION" NUMBER(*,0), | |
| "AG_ROOT_ID" VARCHAR2(100 BYTE), | |
| "AG_MILESTONE_PATH" VARCHAR2(100 BYTE), | |
| "CACHE_VERSION" NUMBER(*,0), | |
| "PARENT_REF_ID" VARCHAR2(150 BYTE), | |
| "COMPONENTTYPE" VARCHAR2(10 BYTE), | |
| "NOTM" NUMBER(*,0), | |
| "COMPOSITE_NAME" VARCHAR2(500 BYTE), | |
| "DOMAIN_NAME" VARCHAR2(50 BYTE), | |
| "COMPONENT_NAME" VARCHAR2(300 BYTE), | |
| "COMPOSITE_LABEL" VARCHAR2(50 BYTE), | |
| "COMPOSITE_REVISION" VARCHAR2(50 BYTE), | |
| "CREATE_CLUSTER_NODE_ID" VARCHAR2(100 BYTE), | |
| "LAST_CLUSTER_NODE_ID" VARCHAR2(100 BYTE), | |
| "CPST_INST_CREATED_TIME" TIMESTAMP (6) DEFAULT systimestamp, | |
| "TENANT_ID" NUMBER(18,0) DEFAULT -1 | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (CPST_INST_CREATED_TIME) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("COMPOSITE_REVISION" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("COMPONENT_NAME" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("COMPOSITE_NAME" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("COMPONENTTYPE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("CREATION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" MODIFY ("CIKEY" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.CUBE_INSTANCE SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."CUBE_INSTANCE" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."CUBE_INSTANCE_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."CI_PK" ON "YOUR_SOA_USER"."CUBE_INSTANCE" ("CIKEY") REVERSE | |
| GLOBAL PARTITION BY HASH(CIKEY) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."CI_ECID" ON "YOUR_SOA_USER"."CUBE_INSTANCE" ("ECID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."CI_CUSTOM3" ON "YOUR_SOA_USER"."CUBE_INSTANCE" ("TEST_RUN_ID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."CI_CREATION_DATE" ON "YOUR_SOA_USER"."CUBE_INSTANCE" ("CREATION_DATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."CI_NAME_REV_STATE" ON "YOUR_SOA_USER"."CUBE_INSTANCE" ("DOMAIN_NAME", "COMPOSITE_NAME", "COMPONENT_NAME", "COMPOSITE_REVISION", "STATE") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_INSTANCE" ADD CONSTRAINT "CI_PK" PRIMARY KEY ("CIKEY") USING INDEX ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table COMPOSITE_INSTANCE_ASSOC | |
| -------------------------------------------------------- | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC1_ECID"; | |
| DROP INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC2_ECID"; | |
| ALTER TABLE "COMPOSITE_INSTANCE_ASSOC" RENAME TO "COMPOSITE_INSTANCE_ASSOC_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" | |
| ( "ASSOC1_ECID" VARCHAR2(100 BYTE), | |
| "ASSOC1_COMPOSITE_INSTANCE_ID" NUMBER(*,0), | |
| "ASSOC1_COMPONENT_INSTANCE_ID" VARCHAR2(100 BYTE), | |
| "ASSOC2_ECID" VARCHAR2(100 BYTE), | |
| "ASSOC2_COMPOSITE_INSTANCE_ID" NUMBER(*,0), | |
| "ASSOC2_COMPONENT_INSTANCE_ID" VARCHAR2(100 BYTE), | |
| "CREATED_TIME" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| PARTITION BY RANGE (CREATED_TIME) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC2_COMPONENT_INSTANCE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC2_COMPOSITE_INSTANCE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC2_ECID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC1_COMPONENT_INSTANCE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC1_COMPOSITE_INSTANCE_ID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" MODIFY ("ASSOC1_ECID" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.COMPOSITE_INSTANCE_ASSOC SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC_SAV"; | |
| COMMIT; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC1_ECID" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" ("ASSOC1_ECID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| CREATE INDEX "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC2_ECID" ON "YOUR_SOA_USER"."COMPOSITE_INSTANCE_ASSOC" ("ASSOC2_ECID") | |
| LOCAL NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| -------------------------------------------------------- | |
| -- PARTITIONING for Table CUBE_SCOPE | |
| -------------------------------------------------------- | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_SCOPE" DROP CONSTRAINT "CS_PK"; | |
| ALTER TABLE "CUBE_SCOPE" RENAME TO "CUBE_SCOPE_SAV"; | |
| CREATE TABLE "YOUR_SOA_USER"."CUBE_SCOPE" | |
| ( "CIKEY" NUMBER(*,0), | |
| "BINARY_FORMAT" NUMBER(*,0), | |
| "MODIFY_DATE" TIMESTAMP (6), | |
| "SCOPE_BIN" BLOB, | |
| "CI_PARTITION_DATE" TIMESTAMP (6) | |
| ) SEGMENT CREATION IMMEDIATE | |
| PCTFREE 10 PCTUSED 1 INITRANS 1 MAXTRANS 255 | |
| NOCOMPRESS LOGGING | |
| STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
| TABLESPACE "YOUR_USER_TABLESPACE" | |
| LOB ("SCOPE_BIN") STORE AS SECUREFILE ( | |
| TABLESPACE "YOUR_USER_TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192 | |
| CACHE NOCOMPRESS KEEP_DUPLICATES | |
| STORAGE(INITIAL 106496 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645 | |
| PCTINCREASE 0 | |
| BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) | |
| PARTITION BY RANGE (CI_PARTITION_DATE) | |
| ( | |
| PARTITION P_MAX VALUES LESS THAN (MAXVALUE) | |
| ) | |
| ; | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_SCOPE" MODIFY ("CI_PARTITION_DATE" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_SCOPE" MODIFY ("CIKEY" NOT NULL ENABLE); | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_SCOPE" ENABLE ROW MOVEMENT; | |
| begin | |
| for c in (select distinct trunc(PARTITION_DATE,'DDD') dt from YOUR_SOA_USER.COMPOSITE_INSTANCE order by 1) loop | |
| execute immediate 'ALTER TABLE YOUR_SOA_USER.CUBE_SCOPE SPLIT PARTITION P_MAX at (to_date(' || '''' || to_char(c.dt+1,'DD/MM/YYYY') || '''' || ',''DD/MM/YYYY'')) INTO (PARTITION P_' || to_char(c.dt,'YYYYMMDD') ||', PARTITION P_MAX)'; | |
| end loop; | |
| end; | |
| / | |
| INSERT /*+ APPEND NOLOGGING */ INTO "YOUR_SOA_USER"."CUBE_SCOPE" SELECT /*+ PARALLEL(8) */ * FROM "YOUR_SOA_USER"."CUBE_SCOPE_SAV"; | |
| COMMIT; | |
| CREATE UNIQUE INDEX "YOUR_SOA_USER"."CS_PK" ON "YOUR_SOA_USER"."CUBE_SCOPE" ("CIKEY") | |
| GLOBAL PARTITION BY HASH(CIKEY) | |
| PARTITIONS 16 NOLOGGING PARALLEL 8 | |
| TABLESPACE "YOUR_USER_TABLESPACE" ; | |
| ALTER TABLE "YOUR_SOA_USER"."CUBE_SCOPE" ADD CONSTRAINT "CS_PK" PRIMARY KEY ("CIKEY") USING INDEX; | |
| -------------------------------------------------------- | |
| -- INDEX LOGGING NOPARALLEL | |
| -------------------------------------------------------- | |
| begin | |
| for c in (select owner,index_name from all_indexes where trim(degree) not in ('0','1') and owner = 'YOUR_SOA_USER' ) loop | |
| execute immediate 'alter index ' || c.owner || '.' || c.index_name || ' logging noparallel'; | |
| end loop; | |
| end; | |
| / | |
| -------------------------------------------------------- | |
| -- SET TABLE PREFERENCES | |
| -------------------------------------------------------- | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','COMPOSITE_INSTANCE','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','AUDIT_TRAIL','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','XML_DOCUMENT','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','HEADERS_PROPERTIES','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','REFERENCE_INSTANCE','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','DLV_SUBSCRIPTION','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','DLV_MESSAGE','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','WORK_ITEM','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','DOCUMENT_DLV_MSG_REF','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','CUBE_INSTANCE','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','COMPOSITE_INSTANCE_ASSOC','GRANULARITY','GLOBAL'); | |
| exec dbms_stats.set_table_prefs('YOUR_SOA_USER','CUBE_SCOPE','GRANULARITY','GLOBAL'); | |
| -------------------------------------------------------- | |
| -- GATHER STATS | |
| -------------------------------------------------------- | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','COMPOSITE_INSTANCE',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>12); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','AUDIT_TRAIL',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>12); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','XML_DOCUMENT',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','HEADERS_PROPERTIES',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','REFERENCE_INSTANCE',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','DLV_SUBSCRIPTION',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','DLV_MESSAGE',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','WORK_ITEM',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','DOCUMENT_DLV_MSG_REF',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','CUBE_INSTANCE',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','COMPOSITE_INSTANCE_ASSOC',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>12); | |
| exec dbms_stats.gather_table_stats('YOUR_SOA_USER','CUBE_SCOPE',estimate_percent=>100,granularity=>'GLOBAL',method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>4); | |
| spool off; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment