ETL 9.2: Descending Indexes Causes The Error ORA-00600 (Doc ID 1954019.1)

Last updated on MAY 25, 2016

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Goal

As delivered in Time and Labor, PS_TL_FRCS_PYBL_TM has a one of the columns set to Descending in its index. As a result of this, the following statement causes an ORA-00600 error in the database: "DELETE FROM PS_TL_FRCS_PYBL_TM WHERE EXISTS ( SELECT 'X' FROM PS_TL_PROF_LIST3 PL WHERE PL.PROCESS_INSTANCE = 208720 AND PL.EMPLID = PS_TL_FRCS_PYBL_TM.EMPLID AND PL.EMPL_RCD = PS_TL_FRCS_PYBL_TM.EMPL_RCD AND PS_TL_FRCS_PYBL_TM.DUR BETWEEN PL.START_DT AND PL.END_DT)."

We have found that if the parameter on the database _ignore_desc_in_index is set to TRUE, then we don't get an ORA-00600 error on the above mentioned DELETE statement. However, when _ignore_desc_in_index=TRUE, PeopleTools Application Designer does not recognize any indexes that contain a column that is descending. When the Index Create Options on the Create tab is set to "Recreate index only if modified", any indexes with a descending column will continually show up. An example of this is the index on PS_TL_FRCS_PYBL_TM, where the DUR field is descending.

We have found in the attachment on DocID 1100831.1 that Descending Indices for PeopleSoft Databases should be removed. However, this index is delivered by the Time and Labor Module.

If it should be removed, shouldn't that come out as a patch from that team?

The basic issue is, is that the descending PS_TL_FRCS_PYBL_TM index causes issues regardless of setting _ignore_desc_in_index = true or false. This descending index is delivered by Time and Labor. If having a descending index is the issue, then it should be removed via a patch by the application that put it in (Time and Labor).

We are on PeopleTools 8.53.10, and Oracle 11.2.0.3 on Windows 2008 R2. PeopleSoft HR 9.2 through PUM8.
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms