Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
--------------------------------------------------------
-- 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