Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle B2B
11g Release 1 (11.1.1.7)

Part Number E10229-13
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

I Database Partitioning

This appendix discusses the partitioning of the Oracle B2B instance tables.

The appendix contains the following sections:

I.1 Introduction

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:

I.2 Partitioning Requirements

The following are the requirements that you need to adhere for successful partitioning of the Oracle B2B tables:

I.3 Partitioning the Tables

To partition the tables, use the following scripts:

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;