Index Creation On CI_TXN_DETAIL Table Stuck While Upgrading To Version ORMB V4.0.0.0.0 (Doc ID 2903395.1)

Last updated on OCTOBER 24, 2022

Applies to:

Goal

On RMB v4.0.0.0.0, Index creation stuck on CI_TXN_DETAIL table.

Upgrade to V4.0.0.0.0 stuck at below line :
- 2022-07-19 17:25:26,834 [main] DEBUG (oem.install.OraSchUpg)
CREATE INDEX XF238S3 ON CI_TXN_DETAIL(CURR_SYS_PRCS_DT,TXN_DETAIL_ID,BO_STATUS_CD,DISAGG_SW,TXN_HEADER_ID,TXN_SOURCE_CD,CIS_DIVISION) Tablespace CISTS_01 ;

table CI_TXN_DETAIL contains 0.374 billion records.
Upgrade script does not move forward from this step.

We found a solution for this in older RMB releases and tried ti as follow:

--------------------
Update ~RMB/Upgrade/Oracle/Install-Upgrad/CDXSchUpg.inp file to add the below line to exclude the specified index
-INDEX: 'XF238S3'

Specific Index creation SQL to be used based on below conditions:

Normal Index creation:
CREATE INDEX XF238S3 ON
CI_TXN_DETAIL(CURR_SYS_PRCS_DT,TXN_DETAIL_ID,BO_STATUS_CD,DISAGG_SW,TXN_HEADER_ID,TXN_SOURCE_CD,CIS_DIVISION) INITRANS 40;

If table is partitioned use below partition Index creation sql:
CREATE INDEX XF238S3 ON CI_TXN_DETAIL (CURR_SYS_PRCS_DT, TXN_DETAIL_ID,
BO_STATUS_CD, DISAGG_SW, TXN_HEADER_ID, TXN_SOURCE_CD, CIS_DIVISION) LOCAL
INITRANS 40;
--------------------

But after trying the suggested workaround to exclude the INDEX, we still see that index creation process continue as part of upgrade script.
We find the line -INDEX: 'XF238S3' added in file ~RMB/Upgrade/Oracle/Install-Upgrad/CDXSchUpg.inp.

Even following the steps, still this index creation is not getting skipped, rather getting created while running upgrade.
But we do not want to have the Index because we have huge data inside CI_TXN_DETAIL table which gets impacted for this indexing.

Please advise how to move forward so that index creation gets skipped.
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.