Sample SQL for Enabling ILM with Sub Retention in SGG (Existing Installation)
This section provides additional details including the sample syntax for each step using the Initial Measurement Data maintenance object as an example. Other maintenance object's implementations can follow a similar pattern.
1. Rename existing D1_INIT_MSRMT_DATA tables and primary key indexes and constraints as a backup. It is suggested to use an ILM_ prefix. The following are sample statements:
ALTER TABLE D1_INIT_MSRMT_DATA RENAME TO ILM_D1_INIT_MSRMT_DATA;
ALTER TABLE D1_INIT_MSRMT_DATA RENAME CONSTRAINT D1T304P0 TO ILM_D1T304P0;
ALTER INDEX D1T304P0 RENAME TO ILM_D1T304P0;
ALTER TABLE D1_INIT_MSRMT_DATA_CHAR RENAME TO ILM_D1_INIT_MSRMT_DATA_CHAR;
ALTER TABLE D1_INIT_MSRMT_DATA_CHAR RENAME CONSTRAINT D1T305P0 TO ILM_D1T305P0;
ALTER INDEX D1T305P0 RENAME TO ILM_D1T305P0;
ALTER TABLE D1_INIT_MSRMT_DATA_LOG RENAME TO ILM_D1_INIT_MSRMT_DATA_LOG;
ALTER TABLE D1_INIT_MSRMT_DATA_LOG RENAME CONSTRAINT D1T306P0 TO ILM_D1T306P0;
ALTER INDEX D1T306P0 RENAME TO ILM_D1T306P0;
ALTER TABLE D1_INIT_MSRMT_DATA_LOG_PARM RENAME TO ILM_D1_INIT_MSRMT_DATA_LOG_PARM;
ALTER TABLE D1_INIT_MSRMT_DATA_LOG_PARM RENAME CONSTRAINT D1T307P0 TO ILM_D1T307P0;
ALTER INDEX D1T307P0 RENAME TO ILM_D1T307P0;
ALTER TABLE D1_INIT_MSRMT_DATA_K RENAME TO ILM_D1_INIT_MSRMT_DATA_K;
ALTER TABLE D1_INIT_MSRMT_DATA_K RENAME CONSTRAINT D1T314P0 TO ILM_D1T314P0;
ALTER INDEX D1T314P0 RENAME TO ILM_D1T314P0;
2. Generate DDL for the secondary index.
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('INDEX','D1T304S1','CISADM') from dual;
spool off;
3. Drop secondary indexes.
DROP INDEX CISADM.D1T304S1;
4. Create Partitioned Table.
In the following example ILM_DT value is inserted from column CRE_DTTM. The degree setting of 'parallel' in the DDL can be adjusted according to the table’s data, its means and its size. Use the CTAS queries listed in Chapter 5 to create temporary tables for ACTIVITY, DEVICE EVENT, and INITIAL MEASUREMENT DATA and use the following statements to create the partitioned tables.
Activity
CREATE TABLE D1_ACTIVITY (
D1_ACTIVITY_ID NOT NULL,
BUS_OBJ_CD NOT NULL,
BO_STATUS_CD NOT NULL,
ACTIVITY_TYPE_CD NOT NULL,
START_DTTM NOT NULL,
END_DTTM,
CRE_DTTM NOT NULL,
STATUS_UPD_DTTM NOT NULL,
BO_STATUS_REASON_CD NOT NULL,
VERSION NOT NULL,
EFF_DTTM,
BO_DATA_AREA,
FIELD_TASK_TYPE,
CANCEL_REASON,
ILM_DT,
ILM_ARCH_SW,
RETENTION_PERIOD NOT NULL
)
AS
SELECT
A.D1_ACTIVITY_ID,
A.BUS_OBJ_CD,
A.BO_STATUS_CD,
A.ACTIVITY_TYPE_CD,
A.START_DTTM,
A.END_DTTM,
A.CRE_DTTM,
A.STATUS_UPD_DTTM,
A.BO_STATUS_REASON_CD,
A.VERSION,
A.EFF_DTTM,
A.BO_DATA_AREA,
A.FIELD_TASK_TYPE,
A.CANCEL_REASON,
A.CRE_DTTM as ILM_DT,
'N' as ILM_ARCH_SW,
CAST(COALESCE((SELECT B.RETPERIOD
FROM ILM_ACTIVITY_RETENTION_TMP B
WHERE B.ACTIVITY_TYPE_CD = A.ACTIVITY_TYPE_CD)
,CAST((select maint_obj_opt_val
from ci_md_mo_opt mmouni
where maint_obj_cd = 'D1-ACTIVITY'
and maint_obj_opt_flg ='FLRP'
and seq_num =
(select max(seq_num)
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-ACTIVITY'
and maint_obj_opt_flg ='FLRP')) as NUMBER(5))
,CAST((select extractvalue( xmlparse(content fw_mcfg.mst_config_data)
,'generalMasterConfiguration/defaultRetentionPeriod')
from f1_mst_config fw_mcfg
where fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig') as NUMBER(5))
, 99999) as NUMBER(5)) as RETENTION_PERIOD
FROM ILM_D1_ACTIVITY A
/
Device Event
CREATE TABLE D1_DVC_EVT(
DVC_EVT_ID NOT NULL,
DVC_EVT_TYPE_CD,
BUS_OBJ_CD NOT NULL,
EXT_EVT_NAME_FLG,
D1_SPR_CD,
BO_STATUS_CD NOT NULL,
STATUS_UPD_DTTM NOT NULL,
BO_STATUS_REASON_CD NOT NULL,
DVC_EVT_DTTM NOT NULL,
CRE_DTTM NOT NULL,
VERSION NOT NULL,
DVC_EVT_END_DTTM,
BO_DATA_AREA,
D1_DEVICE_ID,
ILM_DT NOT NULL,
ILM_ARCH_SW,
RETENTION_PERIOD NOT NULL)
AS
SELECT
A.DVC_EVT_ID,
A.DVC_EVT_TYPE_CD,
A.BUS_OBJ_CD,
A.EXT_EVT_NAME_FLG,
A.D1_SPR_CD,
A.BO_STATUS_CD,
A.STATUS_UPD_DTTM,
A.BO_STATUS_REASON_CD,
A.DVC_EVT_DTTM,
A.CRE_DTTM,
A.VERSION,
A.DVC_EVT_END_DTTM,
A.BO_DATA_AREA,
A.D1_DEVICE_ID,
A.CRE_DTTM as ILM_DT,
'N' as ILM_ARCH_SW,
CAST(COALESCE((SELECT B.RETPERIOD
FROM ILM_DVC_EVT_RETENTION_TMP B
WHERE B.DVC_EVT_TYPE_CD = A.DVC_EVT_TYPE_CD)
,CAST((select maint_obj_opt_val
from ci_md_mo_opt mmouni
where maint_obj_cd = 'D1-DVCEVENT'
and maint_obj_opt_flg ='FLRP'
and seq_num =
(select max(seq_num)
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-DVCEVENT'
and maint_obj_opt_flg ='FLRP')) as NUMBER(5))
,CAST((select extractvalue( xmlparse(content fw_mcfg.mst_config_data)
,'generalMasterConfiguration/defaultRetentionPeriod')
from f1_mst_config fw_mcfg
where fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig') as NUMBER(5))
, 99999) as NUMBER(5)) as RETENTION_PERIOD
FROM ILM_D1_DVC_EVT A
/
Initial Measurement Data
CREATE TABLE ILM_IMD_RETENTION_TMP
AS
select mct.measr_comp_type_cd
/*retrieve the retention period for MC Types in this order of precedence:
1. The UOM based retention period from the MDM master configuration
2. The interval IMD retention period from the MDM master configuration
3. The MO level retention period from the MO options
4. The installation level retention period from the FW master configuration
*/
, CAST(coalesce( (select retPeriod
from (select 'D1IN' interval_scalar_flg
, extractvalue(value(p),'uomRetentionPeriodList/uom') D1_UOM_CD
, extractvalue(value(p),'uomRetentionPeriodList/retentionPeriod') retPeriod
from f1_mst_config mdm_mcfg
, table(xmlsequence(extract(xmlparse(content
mdm_mcfg.mst_config_data),
'imdRetentionPeriod/intervalImdRetentionPeriods/uomRetentionPeriods/uomRetentionPeriodList'))) p
where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig'
union
select 'D1SC' INTERVAL_SCALAR_FLG
, extractvalue(value(p),'uomRetentionPeriodList/uom') D1_UOM_CD
, extractvalue(value(p),'uomRetentionPeriodList/retentionPeriod') retPeriod
from f1_mst_config mdm_mcfg
, table(xmlsequence(extract(xmlparse(content
mdm_mcfg.mst_config_data),
'imdRetentionPeriod/scalarImdRetentionPeriods/uomRetentionPeriods/uomRetentionPeriodList'))) p
where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig') uomMap
where uomMap.interval_scalar_flg = mct.interval_scalar_flg
and trim(mctvi.d1_uom_cd) = trim(uomMap.d1_uom_cd))--UOM
, DECODE(mct.interval_scalar_flg
,'D1IN'
,extractvalue( xmlparse(content mdm_mcfg.mst_config_data),
'imdRetentionPeriod/intervalImdRetentionPeriods/intervalRetentionPeriod') --interval IMD
,extractvalue( xmlparse(content mdm_mcfg.mst_config_data),
'imdRetentionPeriod/scalarImdRetentionPeriods/scalarRetentionPeriod') --scalar IMD
)
, (select maint_obj_opt_val
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-IMD'
and maint_obj_opt_flg ='FLRP'
and seq_num = (select max(seq_num)
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-IMD'
and maint_obj_opt_flg ='FLRP')) --IMD
, extractvalue( xmlparse(content fw_mcfg.mst_config_data),
'generalMasterConfiguration/defaultRetentionPeriod') --Install
) as NUMBER(5)) retPeriod
from d1_measr_comp_type mct
, d1_mc_type_value_identifier mctvi
, f1_mst_config fw_mcfg
, f1_mst_config mdm_mcfg
where mct.measr_comp_type_cd = mctvi.measr_comp_type_cd
and mctvi.value_id_type_flg = 'D1MS'
and fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig'
and mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig'
order by 1;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JAN_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JAN_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011FEB_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011FEB_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAR_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAR_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011APR_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011APR_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAY_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011MAY_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JUN_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JUN_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JUL_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JUL_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011AUG_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011AUG_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011SEP_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011SEP_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011OCT_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011OCT_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011NOV_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011NOV_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011DEC_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_P2011DEC_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_PMAX_S181 DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE BIGFILE TABLESPACE CM_D1T304_PMAX_SMAX DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
CREATE TABLE D1_INIT_MSRMT_DATA
(
INIT_MSRMT_DATA_ID NOT NULL,
MEASR_COMP_ID NOT NULL,
D1_FROM_DTTM,
D1_TO_DTTM,
DATA_SRC_FLG NOT NULL,
TIME_ZONE_CD NOT NULL,
BUS_OBJ_CD NOT NULL,
BO_STATUS_CD NOT NULL,
BO_STATUS_REASON_CD NOT NULL,
IMD_BO_DATA_AREA,
STATUS_UPD_DTTM NOT NULL,
CRE_DTTM NOT NULL,
VERSION NOT NULL,
IMD_EXT_ID,
PREVEE_BO_DATA_AREA,
POSTVEE_BO_DATA_AREA,
TRACE_BO_DATA_AREA,
RAW_BO_DATA_AREA,
LAST_UPDATE_DTTM,
ILM_DT,
ILM_ARCH_SW,
RETENTION_PERIOD NOT NULL
)
nologging parallel (degree 10)
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 (ILM_DT) SUBPARTITION BY RANGE (RETENTION_PERIOD)
(
PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011JAN_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011JAN_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_S181)
,
SUBPARTITION P2011JAN_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011JAN_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JAN_SMAX)
),
PARTITION "P2011FEB" VALUES LESS THAN (TO_DATE('2011-03-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011FEB_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011FEB_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_S181)
,
SUBPARTITION P2011FEB_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011FEB_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011FEB_SMAX)
),
PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011MAR_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011MAR_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_S181)
,
SUBPARTITION P2011MAR_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011MAR_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAR_SMAX)
),
PARTITION "P2011APR" VALUES LESS THAN (TO_DATE('2011-05-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011APR_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011APR_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_S181)
,
SUBPARTITION P2011APR_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011APR_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011APR_SMAX)
),
PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011MAY_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011MAY_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_S181)
,
SUBPARTITION P2011MAY_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011MAY_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011MAY_SMAX)
),
PARTITION "P2011JUN" VALUES LESS THAN (TO_DATE('2011-07-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011JUN_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011JUN_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_S181)
,
SUBPARTITION P2011JUN_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011JUN_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUN_SMAX)
),
PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011JUL_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011JUL_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_S181)
,
SUBPARTITION P2011JUL_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011JUL_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011JUL_SMAX)
),
PARTITION "P2011AUG" VALUES LESS THAN (TO_DATE('2011-09-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011AUG_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011AUG_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_S181)
,
SUBPARTITION P2011AUG_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011AUG_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011AUG_SMAX)
),
PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011SEP_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011SEP_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_S181)
,
SUBPARTITION P2011SEP_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011SEP_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011SEP_SMAX)
),
PARTITION "P2011OCT" VALUES LESS THAN (TO_DATE('2011-11-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011OCT_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011OCT_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_S181)
,
SUBPARTITION P2011OCT_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011OCT_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011OCT_SMAX)
),
PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011NOV_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011NOV_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_S181)
,
SUBPARTITION P2011NOV_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011NOV_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011NOV_SMAX)
),
PARTITION "P2011DEC" VALUES LESS THAN (TO_DATE('2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(
SUBPARTITION P2011DEC_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_P2011DEC_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_S181)
,
SUBPARTITION P2011DEC_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_P2011DEC_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_P2011DEC_SMAX)
),
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)(
SUBPARTITION PMAX_S181 VALUES LESS THAN (181) TABLESPACE CM_D1T304_PMAX_S181
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_S181)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_S181)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_S181)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_S181)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_S181)
,
SUBPARTITION PMAX_SMAX VALUES LESS THAN (MAXVALUE) TABLESPACE CM_D1T304_PMAX_SMAX
LOB (PREVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_SMAX)
LOB (POSTVEE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_SMAX)
LOB (TRACE_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_SMAX)
LOB (RAW_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_SMAX)
LOB (IMD_BO_DATA_AREA) STORE AS SECUREFILE (TABLESPACE CM_D1T304_PMAX_SMAX)
)) ENABLE ROW MOVEMENT AS
SELECT
A.INIT_MSRMT_DATA_ID,
A.MEASR_COMP_ID,
A.D1_FROM_DTTM,
A.D1_TO_DTTM,
A.DATA_SRC_FLG,
A.TIME_ZONE_CD,
A.BUS_OBJ_CD,
A.BO_STATUS_CD,
A.BO_STATUS_REASON_CD,
A.IMD_BO_DATA_AREA,
A.STATUS_UPD_DTTM,
A.CRE_DTTM,
A.VERSION,
A.IMD_EXT_ID,
A.PREVEE_BO_DATA_AREA,
A.POSTVEE_BO_DATA_AREA,
A.TRACE_BO_DATA_AREA,
A.RAW_BO_DATA_AREA,
A.LAST_UPDATE_DTTM,
A.CRE_DTTM as ILM_DT,
'N' as ILM_ARCH_SW,
CAST(COALESCE((SELECT C.RETPERIOD
FROM D1_MEASR_COMP B, ILM_IMD_RETENTION_TMP C
WHERE B.MEASR_COMP_ID = A.MEASR_COMP_ID
AND C.MEASR_COMP_TYPE_CD = B.MEASR_COMP_TYPE_CD)
,CAST((select maint_obj_opt_val
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-IMD'
and maint_obj_opt_flg ='FLRP'
and seq_num =
(select max(seq_num)
from ci_md_mo_opt mmo
where maint_obj_cd = 'D1-IMD'
and maint_obj_opt_flg ='FLRP')) as NUMBER(5))
,CAST((select extractvalue( xmlparse(content fw_mcfg.mst_config_data)
,'generalMasterConfiguration/defaultRetentionPeriod')
from f1_mst_config fw_mcfg
where fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig') as NUMBER(5))
, 99999) as NUMBER(5)) as RETENTION_PERIOD
FROM ILM_D1_INIT_MSRMT_DATA A
/
5. Enable logging option for table D1_INIT_MSRMT_DATA.
ALTER TABLE D1_INIT_MSRMT_DATA NOPARALLEL LOGGING;
6. Create Primary Index for Parent table D1_INIT_MSRMT_DATA.
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 NOLOGGING PARALLEL (INIT_MSRMT_DATA_ID)
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)
) COMPRESS ADVANCED LOW
/
ALTER INDEX D1T304P0 LOGGING NOPARALLEL;
7. Add Primary Key for Parent table D1_INIT_MSRMT_DATA
ALTER TABLE D1_INIT_MSRMT_DATA ADD CONSTRAINT D1T304P0 PRIMARY KEY(INIT_MSRMT_DATA_ID) USING INDEX
/
8. Create Secondary Indexes for Parent table D1_INIT_MSRMT_DATA
CREATE INDEX D1T304S1 ON D1_INIT_MSRMT_DATA (MEASR_COMP_ID, D1_TO_DTTM)
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
/
CREATE UNIQUE INDEX CM_ILM_D1T304S4 ON D1_INIT_MSRMT_DATA (ILM_DT, RETENTION_PERIOD, ILM_ARCH_SW, INIT_MSRMT_DATA_ID) LOCAL COMPRESS ADVANCED LOW
/
9. Create Child Tables, Primary Key, Primary Indexes and Secondary Indexes as shown below.
Create Child Table D1_INIT_MSRMT_DATA_CHAR
CREATE TABLE D1_INIT_MSRMT_DATA_CHAR
(
INIT_MSRMT_DATA_ID NOT NULL ENABLE,
CHAR_TYPE_CD NOT NULL ENABLE,
SEQ_NUM NOT NULL ENABLE,
CHAR_VAL DEFAULT ' ' NOT NULL ENABLE,
ADHOC_CHAR_VAL DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK1 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK2 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK3 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK4 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK5 DEFAULT ' ' NOT NULL ENABLE,
SRCH_CHAR_VAL DEFAULT ' ' NOT NULL ENABLE,
VERSION DEFAULT 1 NOT NULL ENABLE,
LAST_UPDATE_DTTM ,
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 NOLOGGING PARALLEL
AS SELECT /*+ PARALLEL */ * FROM ILM_D1_INIT_MSRMT_DATA_CHAR
/
ALTER TABLE D1_INIT_MSRMT_DATA_CHAR LOGGING NOPARALLEL
/
Create Primary Index for Child Table D1_INIT_MSRMT_DATA_CHAR
CREATE UNIQUE INDEX D1T305P0 ON D1_INIT_MSRMT_DATA_CHAR(INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM)
TABLESPACE CM_D1T304_IND NOLOGGING PARALLEL
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 INDEX D1T305P0 LOGGING NOPARALLEL
/
Create Primary Key for Child Table D1_INIT_MSRMT_DATA_CHAR
ALTER TABLE D1_INIT_MSRMT_DATA_CHAR ADD CONSTRAINT D1T305P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM) USING INDEX
/
Create Secondary Indexes for Child Table D1_INIT_MSRMT_DATA_CHAR
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
)
/
Create Child Table D1_INIT_MSRMT_DATA_LOG
CREATE TABLE D1_INIT_MSRMT_DATA_LOG (
INIT_MSRMT_DATA_ID NOT NULL ENABLE,
SEQNO NOT NULL ENABLE,
BO_STATUS_CD DEFAULT ' ' NOT NULL ENABLE,
BO_STATUS_REASON_CD DEFAULT ' ' NOT NULL ENABLE,
CHAR_TYPE_CD DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL DEFAULT ' ' NOT NULL ENABLE,
ADHOC_CHAR_VAL DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK1 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK2 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK3 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK4 DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK5 DEFAULT ' ' NOT NULL ENABLE,
DESCRLONG DEFAULT ' ' NOT NULL ENABLE,
LOG_DTTM NOT NULL ENABLE,
LOG_ENTRY_TYPE_FLG DEFAULT ' ' NOT NULL ENABLE,
MESSAGE_CAT_NBR DEFAULT 0 NOT NULL ENABLE,
MESSAGE_NBR DEFAULT 0 NOT NULL ENABLE,
USER_ID DEFAULT ' ' NOT NULL ENABLE,
VERSION DEFAULT 1 NOT NULL ENABLE,
LAST_UPDATE_DTTM,
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 NOLOGGING PARALLEL
AS SELECT /*+ PARALLEL */ * FROM ILM_D1_INIT_MSRMT_DATA_LOG
/
ALTER TABLE D1_INIT_MSRMT_DATA_LOG LOGGING NOPARALLEL
/
Create Primary Index for Child Table D1_INIT_MSRMT_DATA_LOG
CREATE UNIQUE INDEX D1T306P0 ON D1_INIT_MSRMT_DATA_LOG(INIT_MSRMT_DATA_ID, SEQNO)
TABLESPACE CM_D1T304_IND NOLOGGING PARALLEL
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 INDEX D1T306P0 LOGGING NOPARALLEL
/
Create Primary Key for Child Table D1_INIT_MSRMT_DATA_LOG
ALTER TABLE D1_INIT_MSRMT_DATA_LOG ADD CONSTRAINT D1T306P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, SEQNO) USING INDEX
/
Create Child Table D1_INIT_MSRMT_DATA_LOG_PARM
CREATE TABLE D1_INIT_MSRMT_DATA_LOG_PARM (
INIT_MSRMT_DATA_ID NOT NULL ENABLE,
SEQNO NOT NULL ENABLE,
PARM_SEQ NOT NULL ENABLE,
MSG_PARM_VAL DEFAULT ' ' NOT NULL ENABLE,
MSG_PARM_TYP_FLG DEFAULT ' ' NOT NULL ENABLE,
VERSION DEFAULT 1 NOT NULL ENABLE,
LAST_UPDATE_DTTM ,
CONSTRAINT D1_INIT_MSRMT_DATA_LOG_PARM_FK FOREIGN KEY(INIT_MSRMT_DATA_ID) REFERENCE D1_INIT_MSRMT_DATA ON DELETE CASCADE)
PARTITION BY REFERENCE (D1_INIT_MSRMT_DATA_LOG_PARM_FK) ENABLE ROW MOVEMENT NOLOGGING PARALLEL
AS SELECT /*+ PARALLEL */ * FROM ILM_D1_INIT_MSRMT_DATA_LOG_PARM
/
ALTER TABLE D1_INIT_MSRMT_DATA_LOG_PARM LOGGING NOPARALLEL
/
Create Primary Index for Child Table D1_INIT_MSRMT_DATA_LOG_PARM
CREATE UNIQUE INDEX D1T307P0 ON D1_INIT_MSRMT_DATA_LOG_PARM(INIT_MSRMT_DATA_ID, SEQNO, PARM_SEQ)
TABLESPACE CM_D1T304_IND NOLOGGING PARALLEL 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 INDEX D1T306P0 LOGGING NOPARALLEL
/
Create Primary Key for Child Table D1_INIT_MSRMT_DATA_LOG_PARM
ALTER TABLE D1_INIT_MSRMT_DATA_LOG ADD CONSTRAINT D1T307P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, SEQNO, PARM_SEQ) USING INDEX
/
Create Child Table D1_INIT_MSRMT_DATA_K
CREATE BIGFILE TABLESPACE CM_D1T314_IND DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE TABLE D1_INIT_MSRMT_DATA_K (
INIT_MSRMT_DATA_ID NOT NULL ENABLE,
ENV_ID NOT NULL ENABLE,
CONSTRAINT D1T314P0 PRIMARY KEY (INIT_MSRMT_DATA_ID, ENV_ID) ENABLE
)
ORGANIZATION INDEX
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 )
)
TABLESPACE CM_D1T314_IND
AS SELECT /*+ PARALLEL */ * FROM ILM_D1_INIT_MSRMT_DATA_K
/
ALTER TABLE D1_INIT_MSRMT_DATA_K LOGGING NOPARALLEL
/
10. After verification of the ILM based tables, the user can drop the backup “ILM” renamed tables.