Partitioning Recommendations

In general, the recommendation is for a minimum of 'n' partitions for selective database objects, where 'n' is number of RAC nodes. The specific table level partitioning recommendations are as follows:

• The Table Partitioning scheme for Transaction tables is focused primarily on tables associated with Measurement MO, Measurement Log MO and Initial-Measurement-Data MO.

• D1_MSRMT, D1_MSRMT_CHAR, D1_MSRMT_LOG, D1_MSRMT_LOG_PARM tables can be partitioned by MSRMT_DTTM. Bi-monthly partitions is a good start. Subpartition these tables by MEASR_COMP_ID (8 subpartitions should be a good number to start with).

• D1_INIT_MSRMT_DATA table can be partitioned by D1_TO_DTTM. Bi-monthly partitions is a good start. Subpartition D1_INIT_MSRMT_DATA table by MEASR_COMP_ID (8 subpartitions should be a good number to start with).

• D1_INIT_MSRMT_DATA_CHAR, D1_INIT_MSRMT_DATA_LOG, D1_INIT_MSRMT_DATA_LOG_PARM tables are reference partitioned to the parent table.

• D1_INIT_MSRMT_DATA_K table can be partitioned by INIT_MSRMT_DATA_ID (8 sub partitions should be a good number to start with).

The following sections gives partition recommendation and can be used as reference. Create one tablespace per partition as needed. It includes the following:

• D1_MSRMT

• D1_MSRMT_CHAR

• D1_MSRMT_LOG

• D1_MSRMT_LOG_PARM

• D1_INIT_MSRMT_DATA

• D1_INIT_MSRMT_DATA_CHAR

• D1_INIT_MSRMT_DATA_K

• D1_INIT_MSRMT_DATA_LOG

• D1_INIT_MSRMT_DATA_LOG_PARM

D1_MSRMT

CREATE BIGFILE TABLESPACE CM_D1T298_P2011JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_P2011MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_P2011MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_P2011JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_P2011SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_P2011NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T298_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLE D1_MSRMT (

MEASR_COMP_ID CHAR(12) NOT NULL ENABLE, MSRMT_DTTM DATE NOT NULL ENABLE,

BO_STATUS_CD CHAR(12) DEFAULT ' ' NOT NULL ENABLE, MSRMT_COND_FLG CHAR(6 BYTE) DEFAULT ' ' NOT NULL ENABLE, MSRMT_USE_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE, MSRMT_LOCAL_DTTM DATE NOT NULL ENABLE,

MSRMT_VAL NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, ORIG_INIT_MSRMT_ID CHAR(14) DEFAULT ' ' NOT NULL ENABLE, PREV_MSRMT_DTTM DATE,

MSRMT_VAL1 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL2 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL3 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL4 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL5 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL6 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL7 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL8 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL9 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, MSRMT_VAL10 NUMBER(16,6) DEFAULT 0 NOT NULL ENABLE, BUS_OBJ_CD CHAR(30) DEFAULT ' ' NOT NULL ENABLE, CRE_DTTM DATE NOT NULL ENABLE,

STATUS_UPD_DTTM DATE NOT NULL ENABLE,

USER_EDITED_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE, VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

LAST_UPDATE_DTTM DATE, READING_VAL NUMBER(16,6), COMBINED_MULTIPLIER NUMBER(18,6), READING_COND_FLG CHAR(6)

) ENABLE ROW MOVEMENT

PARTITION BY RANGE (MSRMT_DTTM) SUBPARTITION BY range (MEASR_COMP_ID) SUBPARTITION TEMPLATE(

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '744999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011JAN,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011MAR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011MAY,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011JUL,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011SEP,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T298_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

TABLESPACE CM_D1T298_PMAX

);

CREATE UNIQUE INDEX D1T298P0 ON D1_MSRMT(MEASR_COMP_ID, MSRMT_DTTM) LOCAL COMPRESS ADVANCED LOW;

ALTER TABLE D1_MSRMT ADD CONSTRAINT D1T298P0 PRIMARY KEY(MEASR_COMP_ID, MSRMT_DTTM) USING INDEX;

D1_MSRMT_CHAR

CREATE BIGFILE TABLESPACE CM_D1T299_P2011JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_P2011MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_P2011MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_P2011JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_P2011SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_P2011NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T299_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLE D1_MSRMT_CHAR (

MEASR_COMP_ID CHAR(12) NOT NULL ENABLE, MSRMT_DTTM DATE NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) NOT NULL ENABLE, SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

CHAR_VAL CHAR(16) DEFAULT ' ' NOT NULL ENABLE, ADHOC_CHAR_VAL VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK1 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK2 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK3 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK4 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK5 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, SRCH_CHAR_VAL VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

LAST_UPDATE_DTTM DATE, READING_VAL NUMBER(16,6), COMBINED_MULTIPLIER NUMBER(18,6), READING_COND_FLG CHAR(6)

) ENABLE ROW MOVEMENT

PARTITION BY RANGE (MSRMT_DTTM) SUBPARTITION BY range (MEASR_COMP_ID) SUBPARTITION TEMPLATE(

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '744999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011JAN,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011MAR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011MAY,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011JUL,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011SEP,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T299_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

TABLESPACE CM_D1T299_PMAX

);

CREATE BIGFILE TABLESPACE CM_D1T299_IND DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE UNIQUE INDEX D1T299P0 ON D1_MSRMT_CHAR (

MEASR_COMP_ID, MSRMT_DTTM, CHAR_TYPE_CD, SEQ_NUM

) LOCAL COMPRESS ADVANCED LOW;

ALTER TABLE D1_MSRMT_CHAR ADD CONSTRAINT D1T299P0 PRIMARY KEY (MEASR_COMP_ID, MSRMT_DTTM, CHAR_TYPE_CD, SEQ_NUM) USING INDEX ;

CREATE INDEX D1T299S1 ON D1_MSRMT_CHAR(SRCH_CHAR_VAL)

GLOBAL PARTITION BY HASH(SRCH_CHAR_VAL)

(

PARTITION P1 TABLESPACE CM_D1T299_IND,

PARTITION P2 TABLESPACE CM_D1T299_IND,

PARTITION P3 TABLESPACE CM_D1T299_IND,

PARTITION P4 TABLESPACE CM_D1T299_IND,

PARTITION P5 TABLESPACE CM_D1T299_IND,

PARTITION P6 TABLESPACE CM_D1T299_IND,

PARTITION P7 TABLESPACE CM_D1T299_IND,

PARTITION P8 TABLESPACE CM_D1T299_IND

)

TABLESPACE CM_D1T304_IND;

D1_MSRMT_LOG

CREATE BIGFILE TABLESPACE CM_D1T300_P2011JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_P2011MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_P2011MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_P2011JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_P2011SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_P2011NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T300_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLE D1_MSRMT_LOG (

MEASR_COMP_ID CHAR(12), MSRMT_DTTM DATE,

SEQNO NUMBER(5,0),

ORIG_INIT_MSRMT_ID CHAR(14) DEFAULT ' ' NOT NULL ENABLE, BUS_OBJ_CD CHAR(30) DEFAULT ' ' NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL CHAR(16) DEFAULT ' ' NOT NULL ENABLE, ADHOC_CHAR_VAL VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK1 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK2 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK3 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK4 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, CHAR_VAL_FK5 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE, DESCRLONG VARCHAR2(4000) DEFAULT ' ' NOT NULL ENABLE, LOG_DTTM DATE NOT NULL ENABLE,

MESSAGE_CAT_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE, MESSAGE_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE, USER_ID CHAR(8) DEFAULT ' ' NOT

NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

MSRMT_LOG_ENTRY_TYPE_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

BO_DATA_AREA CLOB

)

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

ENABLE ROW MOVEMENT

PARTITION BY RANGE (MSRMT_DTTM) SUBPARTITION BY range (MEASR_COMP_ID) SUBPARTITION TEMPLATE(

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '744999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011JAN )

TABLESPACE CM_D1T300_P2011JAN,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011MAR )

TABLESPACE CM_D1T300_P2011MAR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011MAY )

TABLESPACE CM_D1T300_P2011MAY,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011JUL )

TABLESPACE CM_D1T300_P2011JUL,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011SEP )

TABLESPACE CM_D1T300_P2011SEP,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_P2011NOV )

TABLESPACE CM_D1T300_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

LOB (BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T300_PMAX )

TABLESPACE CM_D1T300_PMAX

);

CREATE UNIQUE INDEX D1T300P0 ON D1_MSRMT_LOG (

MEASR_COMP_ID, MSRMT_DTTM, SEQNO

) LOCAL COMPRESS ADVANCED LOW;

ALTER TABLE D1_MSRMT_LOG ADD CONSTRAINT D1T300P0 PRIMARY KEY (MEASR_COMP_ID, MSRMT_DTTM, SEQNO) USING INDEX ;

D1_MSRMT_LOG_PARM

CREATE BIGFILE TABLESPACE CM_D1T301_P2011JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_P2011MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_P2011MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_P2011JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_P2011SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_P2011NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T301_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLE D1_MSRMT_LOG_PARM (

MEASR_COMP_ID CHAR(12), MSRMT_DTTM DATE,

SEQNO NUMBER(5,0), PARM_SEQ NUMBER(3,0),

MSG_PARM_VAL VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE, MSG_PARM_TYP_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE, VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE

)

ENABLE ROW MOVEMENT

PARTITION BY RANGE (MSRMT_DTTM) SUBPARTITION BY range (MEASR_COMP_ID) SUBPARTITION TEMPLATE(

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '744999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011JAN,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011MAR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011MAY,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011JUL,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011SEP,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_D1T301_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

TABLESPACE CM_D1T301_PMAX

);

CREATE UNIQUE INDEX D1T301P0 ON D1_MSRMT_LOG_PARM (

MEASR_COMP_ID, MSRMT_DTTM, SEQNO, PARM_SEQ

) INDEX LOCAL COMPRESS ADVANCED LOW;

ALTER TABLE D1_MSRMT_LOG_PARM ADD CONSTRAINT D1T301P0 PRIMARY KEY (MEASR_COMP_ID, MSRMT_DTTM, SEQNO, PARM_SEQ) USING INDEX;

D1_INIT_MSRMT_DATA

CREATE BIGFILE TABLESPACE CM_D1T304_P2011JAN DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAR DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAY DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_P2011JUL DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_P2011SEP DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_P2011NOV DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE BIGFILE TABLESPACE CM_D1T304_PMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLE D1_INIT_MSRMT_DATA

(

INIT_MSRMT_DATA_ID CHAR(14) NOT NULL ENABLE,

MEASR_COMP_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

D1_FROM_DTTM DATE,

D1_TO_DTTM DATE,

DATA_SRC_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

TIME_ZONE_CD CHAR(10) DEFAULT ' ' NOT NULL ENABLE,

BUS_OBJ_CD CHAR(30) DEFAULT ' ' NOT NULL ENABLE,

BO_STATUS_CD CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

BO_STATUS_REASON_CD VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

IMD_BO_DATA_AREA CLOB,

STATUS_UPD_DTTM DATE NOT NULL ENABLE,

CRE_DTTM DATE NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

IMD_EXT_ID VARCHAR2(120),

PREVEE_BO_DATA_AREA CLOB,

POSTVEE_BO_DATA_AREA CLOB,

TRACE_BO_DATA_AREA CLOB,

RAW_BO_DATA_AREA CLOB,

LAST_UPDATE_DTTM DATE,

ILM_DT DATE,

ILM_ARCH_SW CHAR(1),

RETENTION_PERIOD NUMBER(5,0) DEFAULT 99999 NOT NULL ENABLE

)

ENABLE ROW MOVEMENT

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

LOB ( POSTVEE_BO_DATA_AREA ) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE)

PARTITION BY RANGE (D1_TO_DTTM)

SUBPARTITION BY range (MEASR_COMP_ID)

SUBPARTITION TEMPLATE(

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '744999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN)

TABLESPACE CM_D1T304_P2011JAN,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR)

TABLESPACE CM_D1T304_P2011MAR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY)

TABLESPACE CM_D1T304_P2011MAY,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL)

TABLESPACE CM_D1T304_P2011JUL,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP)

TABLESPACE CM_D1T304_P2011SEP,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV)

TABLESPACE CM_D1T304_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX)

LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX)

LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX)

LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX)

LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX)

TABLESPACE CM_D1T304_PMAX

);

CREATE BIGFILE TABLESPACE CM_D1T304_IND DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE UNIQUE INDEX D1T304P0 ON D1_INIT_MSRMT_DATA (

INIT_MSRMT_DATA_ID

) TABLESPACE CM_D1T304_IND

GLOBAL PARTITION BY RANGE (INIT_MSRMT_DATA_ID)

(PARTITION P1 values less than ( ‘12499999999999’ ),

PARTITION P2 values less than ( ‘24999999999999’ ),

PARTITION P3 values less than ( ‘37499999999999’ ),

PARTITION P4 values less than ( ‘49999999999999’ ),

PARTITION P5 values less than ( ‘62499999999999’ ),

PARTITION P6 values less than ( ‘74499999999999’ ),

PARTITION P7 values less than ( ‘87499999999999’ ),

PARTITION P8 values less than ( maxvalue ));

ALTER TABLE D1_INIT_MSRMT_DATA ADD CONSTRAINT D1T304P0 PRIMARY KEY (INIT_MSRMT_DATA_ID) USING INDEX ;

CREATE INDEX D1T304S1 ON D1_INIT_MSRMT_DATA (MEASR_COMP_ID, D1_TO_DTTM) TABLESPACE CM_D1T304_IND

GLOBAL PARTITION BY RANGE (MEASR_COMP_ID)

( PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

D1_INIT_MSRMT_DATA_CHAR

CREATE TABLE D1_INIT_MSRMT_DATA_CHAR

(

INIT_MSRMT_DATA_ID CHAR(14) NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) NOT NULL ENABLE,

SEQ_NUM NUMBER(3,0) NOT NULL ENABLE,

CHAR_VAL CHAR(16) DEFAULT ' ' NOT NULL ENABLE,

ADHOC_CHAR_VAL VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK1 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK2 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK3 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK4 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK5 VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

SRCH_CHAR_VAL VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

LAST_UPDATE_DTTM DATE,

CONSTRAINT D1_INIT_MSRMT_DATA_CHAR_FK FOREIGN KEY(INIT_MSRMT_DATA_ID) REFERENCES D1_INIT_MSRMT_DATA ON DELETE CASCADE)

PARTITION BY REFERENCE (D1_INIT_MSRMT_DATA_CHAR_FK)

ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX D1T305P0 ON D1_INIT_MSRMT_DATA_CHAR(INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM) TABLESPACE CM_D1T304_IND

GLOBAL PARTITION BY RANGE(INIT_MSRMT_DATA_ID)

(

PARTITION P1 VALUES LESS THAN ('12499999999999'),

PARTITION P2 VALUES LESS THAN ('24999999999999'),

PARTITION P3 VALUES LESS THAN ('37499999999999'),

PARTITION P4 VALUES LESS THAN ('49999999999999'),

PARTITION P5 VALUES LESS THAN ('62499999999999'),

PARTITION P6 VALUES LESS THAN ('74999999999999'),

PARTITION P7 VALUES LESS THAN ('87499999999999'),

PARTITION P8 VALUES LESS THAN (MAXVALUE)

) COMPRESS ADVANCED LOW;

ALTER TABLE D1_INIT_MSRMT_DATA_CHAR ADD CONSTRAINT D1T305P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM) USING INDEX ;

CREATE INDEX D1T305S1 ON D1_INIT_MSRMT_DATA_CHAR(SRCH_CHAR_VAL)

GLOBAL PARTITION BY HASH(SRCH_CHAR_VAL)

(

PARTITION P1 TABLESPACE CM_D1T304_IND,

PARTITION P2 TABLESPACE CM_D1T304_IND,

PARTITION P3 TABLESPACE CM_D1T304_IND,

PARTITION P4 TABLESPACE CM_D1T304_IND,

PARTITION P5 TABLESPACE CM_D1T304_IND,

PARTITION P6 TABLESPACE CM_D1T304_IND,

PARTITION P7 TABLESPACE CM_D1T304_IND,

PARTITION P8 TABLESPACE CM_D1T304_IND

);

D1_INIT_MSRMT_DATA_K

CREATE TABLE D1_INIT_MSRMT_DATA_K (

INIT_MSRMT_DATA_ID CHAR(14),

ENV_ID NUMBER(6,0) NOT NULL ENABLE,

CONSTRAINT D1T314P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, ENV_ID) ENABLE

)

ORGANIZATION INDEX ENABLE ROW MOVEMENT

PARTITION BY RANGE (INIT_MSRMT_DATA_ID)

(PARTITION P1 values less than ( ‘12499999999999’ ),

PARTITION P2 values less than ( ‘24999999999999’ ),

PARTITION P3 values less than ( ‘37499999999999’ ),

PARTITION P4 values less than ( ‘49999999999999’ ),

PARTITION P5 values less than ( ‘62499999999999’ ),

PARTITION P6 values less than ( ‘74499999999999’ ),

PARTITION P7 values less than ( ‘87499999999999’ ),

PARTITION P8 values less than ( maxvalue ))

TABLESPACE CM_D1T314_IND ;

D1_INIT_MSRMT_DATA_LOG

CREATE TABLE D1_INIT_MSRMT_DATA_LOG

(

INIT_MSRMT_DATA_ID CHAR(14) NOT NULL ENABLE,

SEQNO NUMBER(5,0) NOT NULL ENABLE,

BO_STATUS_CD CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

BO_STATUS_REASON_CD VARCHAR2(30 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL CHAR(16) DEFAULT ' ' NOT NULL ENABLE,

ADHOC_CHAR_VAL VARCHAR2(254 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK1 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK2 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK3 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK4 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,

CHAR_VAL_FK5 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,

DESCRLONG VARCHAR2(4000) DEFAULT ' ' NOT NULL ENABLE,

LOG_DTTM DATE NOT NULL ENABLE,

LOG_ENTRY_TYPE_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

MESSAGE_CAT_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

MESSAGE_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

LAST_UPDATE_DTTM DATE,

CONSTRAINT D1_INIT_MSRMT_DATA_LOG_FK FOREIGN KEY(INIT_MSRMT_DATA_ID) REFERENCES D1_INIT_MSRMT_DATA ON DELETE CASCADE)

PARTITION BY REFERENCE (D1_INIT_MSRMT_DATA_LOG_FK)

ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX D1T306P0 ON D1_INIT_MSRMT_DATA_LOG (INIT_MSRMT_DATA_ID, SEQNO) TABLESPACE CM_D1T304_IND

GLOBAL PARTITION BY RANGE(INIT_MSRMT_DATA_ID)

(

PARTITION P1 VALUES LESS THAN ('12499999999999'),

PARTITION P2 VALUES LESS THAN ('24999999999999'),

PARTITION P3 VALUES LESS THAN ('37499999999999'),

PARTITION P4 VALUES LESS THAN ('49999999999999'),

PARTITION P5 VALUES LESS THAN ('62499999999999'),

PARTITION P6 VALUES LESS THAN ('74999999999999'),

PARTITION P7 VALUES LESS THAN ('87499999999999'),

PARTITION P8 VALUES LESS THAN (MAXVALUE)

)COMPRESS ADVANCED LOW;

ALTER TABLE D1_INIT_MSRMT_DATA_LOG ADD CONSTRAINT D1T306P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, SEQNO) USING INDEX ;

D1_INIT_MSRMT_DATA_LOG_PARM

CREATE TABLE D1_INIT_MSRMT_DATA_LOG_PARM

(

INIT_MSRMT_DATA_ID CHAR(14) NOT NULL ENABLE,

SEQNO NUMBER(5,0) NOT NULL ENABLE,

PARM_SEQ NUMBER(3,0) NOT NULL ENABLE,

MSG_PARM_VAL VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

MSG_PARM_TYP_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

LAST_UPDATE_DTTM DATE,

CONSTRAINT D1_INIT_MSRMT_DATA_LOG_PARM_FK FOREIGN KEY(INIT_MSRMT_DATA_ID) REFERENCES D1_INIT_MSRMT_DATA ON DELETE CASCADE)

PARTITION BY REFERENCE (D1_INIT_MSRMT_DATA_LOG_PARM_FK)

ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX D1T307P0 ON D1_INIT_MSRMT_DATA_LOG_PARM(INIT_MSRMT_DATA_ID, SEQNO, PARM_SEQ) TABLESPACE CM_D1T304_IND

GLOBAL PARTITION BY RANGE(INIT_MSRMT_DATA_ID)

(

PARTITION P1 VALUES LESS THAN ('12499999999999'),

PARTITION P2 VALUES LESS THAN ('24999999999999'),

PARTITION P3 VALUES LESS THAN ('37499999999999'),

PARTITION P4 VALUES LESS THAN ('49999999999999'),

PARTITION P5 VALUES LESS THAN ('62499999999999'),

PARTITION P6 VALUES LESS THAN ('74999999999999'),

PARTITION P7 VALUES LESS THAN ('87499999999999'),

PARTITION P8 VALUES LESS THAN (MAXVALUE)

) COMPRESS ADVANCED LOW;

ALTER TABLE D1_INIT_MSRMT_DATA_LOG_PARM ADD CONSTRAINT D1T307P0

PRIMARY KEY (INIT_MSRMT_DATA_ID, SEQNO, PARM_SEQ) USING INDEX ;