This appendix discusses the partitioning of the Oracle B2B instance tables.
The appendix contains the following sections:
Oracle B2B allows you to partition the Oracle B2B instance tables based on the CPST_INST_CREATED_TIME column. The following tables can be partitioned:
B2B_APP_MESSAGE
B2B_BUSINESS_MESSAGE
B2B_DATA_STORAGE
B2B_EXT_BUSINESS_MESSAGE
B2B_WIRE_MESSAGE
The following are the requirements that you need to adhere for successful partitioning of the Oracle B2B tables:
Use range or interval partitioning to partition the tables.
Ensure that the date ranges specified across all the Oracle B2B tables are the same. This is a prerequisite for purge to function properly.
When using range partitioning, it is recommended to use a default partition to avoid runtime failures.
Re-creation of tables:
The Oracle B2B system materialized view has dependencies on the Oracle B2B instance tables (B2B_BUSINESS_MESSAGE, B2B_EXT_BUSINESS_MESSAGE) and materialized view logs are created on these tables. During the creation of the partitions, the materialized view logs need to be re-created and the system materialized view needs to be fully refreshed.
At the time of upgrade:
It is recommended to perform the partitioning only after the application instances are upgraded to the required version of Oracle SOA Suite, because upgrading the Oracle SOA Suite schema needs to be completed before partitioning. This is done to avoid NULL values being introduced in the CPST_INST_CREATED_TIME column of the database from the older versions of the runtime.
In case there are existing data in these tables that contains NULL values for the CPST_INST_CREATED_TIME columns, you need to update the records to non-NULL values for appropriate partitioning.
To partition the tables, use the following scripts:
For Range Partition:
createpartition.sql
For Interval Partition:
intervalpartition.sql
Open a command prompt and run the following commands:
setenv ORACLE_HOME <path to Oracle DB Home> setenv ORACLE_SID {dbname} setenv PATH <location of PATH variable> sqlplus CONNECT DEV_SOAINFRA cd <path to the partition script> ./createpartition.sql
Following is a sample of the statements that the partition script executes:
//Disables the constraints
ALTER TABLE B2B_APP_MESSAGE DISABLE CONSTRAINT B2B_AM_PAYLOAD_FK;
ALTER TABLE B2B_APP_MESSAGE DISABLE CONSTRAINT B2B_AM_AS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_AS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_PS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_TDS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_NDS_FK;
ALTER TABLE B2B_WIRE_MESSAGE DISABLE CONSTRAINT B2B_WM_PS_FK;
ALTER TABLE B2B_WIRE_MESSAGE DISABLE CONSTRAINT B2B_WM_DS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_AM_FK;
ALTER TABLE B2B_APP_MESSAGE DISABLE CONSTRAINT B2B_AM_BM_FK;
ALTER TABLE B2B_EXT_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_EBM_BM_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_WM_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DISABLE CONSTRAINT B2B_BM_EBM_FK;
//Drops the constraints
ALTER TABLE B2B_APP_MESSAGE DROP CONSTRAINT B2B_AM_PAYLOAD_FK;
ALTER TABLE B2B_APP_MESSAGE DROP CONSTRAINT B2B_AM_AS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_AS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_PS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_TDS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_NDS_FK;
ALTER TABLE B2B_WIRE_MESSAGE DROP CONSTRAINT B2B_WM_PS_FK;
ALTER TABLE B2B_WIRE_MESSAGE DROP CONSTRAINT B2B_WM_DS_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_AM_FK;
ALTER TABLE B2B_APP_MESSAGE DROP CONSTRAINT B2B_AM_BM_FK;
ALTER TABLE B2B_EXT_BUSINESS_MESSAGE DROP CONSTRAINT B2B_EBM_BM_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_WM_FK;
ALTER TABLE B2B_BUSINESS_MESSAGE DROP CONSTRAINT B2B_BM_EBM_FK;
//Drops the materialized view logs
DROP MATERIALIZED VIEW LOG ON B2B_BUSINESS_MESSAGE;
DROP MATERIALIZED VIEW LOG ON B2B_EXT_BUSINESS_MESSAGE;
//takes backup of the partitioned tables
ALTER TABLE B2B_APP_MESSAGE RENAME TO B2B_APP_MESSAGE_BKP;
ALTER TABLE B2B_BUSINESS_MESSAGE RENAME TO B2B_BUSINESS_MESSAGE_BKP;
ALTER TABLE B2B_EXT_BUSINESS_MESSAGE RENAME TO B2B_EXT_BUSINESS_MESSAGE_BKP;
ALTER TABLE B2B_WIRE_MESSAGE RENAME TO B2B_WIRE_MESSAGE_BKP;
ALTER TABLE B2B_DATA_STORAGE RENAME TO B2B_DATA_STORAGE_BKP;
//creates the tables with partitioning.
CREATE TABLE B2B_APP_MESSAGE
PARTITION BY RANGE(CPST_INST_CREATED_TIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION AM_2011 VALUES LESS
THAN(TO_TIMESTAMP('01/01/2012','DD/MM/YYYY')),
PARTITION AM_JAN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/02/2012','DD/MM/YYYY')),
PARTITION AM_FEB_12 VALUES LESS
THAN(TO_TIMESTAMP('01/03/2012','DD/MM/YYYY')),
PARTITION AM_MAR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/04/2012','DD/MM/YYYY')),
PARTITION AM_APR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/05/2012','DD/MM/YYYY')),
PARTITION AM_MAY_12 VALUES LESS
THAN(TO_TIMESTAMP('01/06/2012','DD/MM/YYYY')),
PARTITION AM_JUN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/07/2012','DD/MM/YYYY')),
PARTITION AM_JUL_12 VALUES LESS
THAN(TO_TIMESTAMP('01/08/2012','DD/MM/YYYY')),
PARTITION AM_AUG_12 VALUES LESS
THAN(TO_TIMESTAMP('01/09/2012','DD/MM/YYYY'))
)
AS SELECT * FROM B2B_APP_MESSAGE_BKP;
CREATE TABLE B2B_BUSINESS_MESSAGE
PARTITION BY RANGE(CPST_INST_CREATED_TIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION BM_2011 VALUES LESS
THAN(TO_TIMESTAMP('01/01/2012','DD/MM/YYYY')),
PARTITION BM_JAN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/02/2012','DD/MM/YYYY')),
PARTITION BM_FEB_12 VALUES LESS
THAN(TO_TIMESTAMP('01/03/2012','DD/MM/YYYY')),
PARTITION BM_MAR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/04/2012','DD/MM/YYYY')),
PARTITION BM_APR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/05/2012','DD/MM/YYYY')),
PARTITION BM_MAY_12 VALUES LESS
THAN(TO_TIMESTAMP('01/06/2012','DD/MM/YYYY')),
PARTITION BM_JUN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/07/2012','DD/MM/YYYY')),
PARTITION BM_JUL_12 VALUES LESS
THAN(TO_TIMESTAMP('01/08/2012','DD/MM/YYYY')),
PARTITION BM_AUG_12 VALUES LESS
THAN(TO_TIMESTAMP('01/09/2012','DD/MM/YYYY'))
)
AS SELECT * FROM B2B_BUSINESS_MESSAGE_BKP;
CREATE TABLE B2B_DATA_STORAGE
PARTITION BY RANGE(CPST_INST_CREATED_TIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION DS_2011 VALUES LESS
THAN(TO_TIMESTAMP('01/01/2012','DD/MM/YYYY')),
PARTITION DS_JAN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/02/2012','DD/MM/YYYY')),
PARTITION DS_FEB_12 VALUES LESS
THAN(TO_TIMESTAMP('01/03/2012','DD/MM/YYYY')),
PARTITION DS_MAR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/04/2012','DD/MM/YYYY')),
PARTITION DS_APR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/05/2012','DD/MM/YYYY')),
PARTITION DS_MAY_12 VALUES LESS
THAN(TO_TIMESTAMP('01/06/2012','DD/MM/YYYY')),
PARTITION DS_JUN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/07/2012','DD/MM/YYYY')),
PARTITION DS_JUL_12 VALUES LESS
THAN(TO_TIMESTAMP('01/08/2012','DD/MM/YYYY')),
PARTITION DS_AUG_12 VALUES LESS
THAN(TO_TIMESTAMP('01/09/2012','DD/MM/YYYY'))
)AS SELECT * FROM B2B_DATA_STORAGE_BKP;
CREATE TABLE B2B_EXT_BUSINESS_MESSAGE
PARTITION BY RANGE(CPST_INST_CREATED_TIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION EM_2011 VALUES LESS
THAN(TO_TIMESTAMP('01/01/2012','DD/MM/YYYY')),
PARTITION EM_JAN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/02/2012','DD/MM/YYYY')),
PARTITION EM_FEB_12 VALUES LESS
THAN(TO_TIMESTAMP('01/03/2012','DD/MM/YYYY')),
PARTITION EM_MAR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/04/2012','DD/MM/YYYY')),
PARTITION EM_APR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/05/2012','DD/MM/YYYY')),
PARTITION EM_MAY_12 VALUES LESS
THAN(TO_TIMESTAMP('01/06/2012','DD/MM/YYYY')),
PARTITION EM_JUN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/07/2012','DD/MM/YYYY')),
PARTITION EM_JUL_12 VALUES LESS
THAN(TO_TIMESTAMP('01/08/2012','DD/MM/YYYY')),
PARTITION EM_AUG_12 VALUES LESS
THAN(TO_TIMESTAMP('01/09/2012','DD/MM/YYYY'))
)AS SELECT * FROM B2B_EXT_BUSINESS_MESSAGE_BKP;
CREATE TABLE B2B_WIRE_MESSAGE
PARTITION BY RANGE(CPST_INST_CREATED_TIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION WM_2011 VALUES LESS
THAN(TO_TIMESTAMP('01/01/2012','DD/MM/YYYY')),
PARTITION WM_JAN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/02/2012','DD/MM/YYYY')),
PARTITION WM_FEB_12 VALUES LESS
THAN(TO_TIMESTAMP('01/03/2012','DD/MM/YYYY')),
PARTITION WM_MAR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/04/2012','DD/MM/YYYY')),
PARTITION WM_APR_12 VALUES LESS
THAN(TO_TIMESTAMP('01/05/2012','DD/MM/YYYY')),
PARTITION WM_MAY_12 VALUES LESS
THAN(TO_TIMESTAMP('01/06/2012','DD/MM/YYYY')),
PARTITION WM_JUN_12 VALUES LESS
THAN(TO_TIMESTAMP('01/07/2012','DD/MM/YYYY')),
PARTITION WM_JUL_12 VALUES LESS
THAN(TO_TIMESTAMP('01/08/2012','DD/MM/YYYY')),
PARTITION WM_AUG_12 VALUES LESS
THAN(TO_TIMESTAMP('01/09/2012','DD/MM/YYYY'))
)AS SELECT * FROM B2B_WIRE_MESSAGE_BKP;
//Creates the materialized view logs
CREATE MATERIALIZED VIEW LOG ON B2B_BUSINESS_MESSAGE WITH ROWID,SEQUENCE
(ext_business_message, channel_name, direction, created,
native_msg_size, doctype_name, doc_protocol_version, doc_protocol_name)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON B2B_EXT_BUSINESS_MESSAGE WITH ROWID, SEQUENCE
(id, processing_time, error_code) INCLUDING NEW VALUES;
//refresh the materialized view logs
EXEC b2b_refresh_mv;
//Drops the backup tables
DROP TABLE B2B_APP_MESSAGE_BKP PURGE;
DROP TABLE B2B_BUSINESS_MESSAGE_BKP PURGE;
DROP TABLE B2B_EXT_BUSINESS_MESSAGE_BKP PURGE;
DROP TABLE B2B_WIRE_MESSAGE_BKP PURGE;
DROP TABLE B2B_DATA_STORAGE_BKP PURGE;