SCC_SL_SMSET - ORA-01652: Unable To Extend Temp Segment By 16 In Tablespace PSTEMP (Doc ID 2824232.1)

Last updated on MAY 30, 2023

Applies to:

Symptoms

SCC_SL_SMSET process has gone to No Success after running for around 1.5 hours.  Last time, it happened,  Temp space was increased to 200GB and database stats were also run. Following error is seen in the log file:


Error Message:

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1738 Error Position: 12 Return: 1652 - ORA-01652: unable to extend temp segment by 16 in tablespace PSTEMP
Failed SQL stmt: INSERT INTO PS_SCC_SLSM1_TMP4 (PROCESS_INSTANCE, SCC_TEMP_ID, EMPLID, SCC_STG_STATUS, SCC_MULT_EMPLIDS) SELECT MAX(CANDS.PROCESS_INSTANCE), MAX(CANDS.SCC_TEMP_ID), MAX(PS_PERSON.EMPLID), MAX('UE'), MAX('N') FROM PS_SCC_SLSM4_TMP4 CANDS, PS_PERSON, PS_SCC_STG_CONSTIT, PS_NAMES, PS_SCC_STG_NAMES, PS_PERS_DATA_EFFDT, PS_SCC_STG_PDE, PS_PERSONAL_PHONE, PS_SCC_STG_PHONE WHERE PS_PERSON.EMPLID = PS_NAMES.EMPLID AND PS_SCC_STG_NAMES.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND PS_SCC_STG_CONSTIT.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND CANDS.PROCESS_INSTANCE = 4886856 AND NVL(RTRIM(SUBSTR(PS_NAMES.FIRST_NAME_SRCH,1,5)), ' ') = NVL(RTRIM(SUBSTR(PS_SCC_STG_NAMES.FIRST_NAME_SRCH,1,5)), ' ') AND PS_NAMES.FIRST_NAME_SRCH <> ' ' AND PS_SCC_STG_NAMES.FIRST_NAME_SRCH <> ' ' AND NVL(RTRIM(SUBSTR(PS_NAMES.LAST_NAME_SRCH,1,5)), ' ') = NVL(RTRIM(SUBSTR(PS_SCC_STG_NAMES.LAST_NAME_SRCH,1,5)), ' ') AND PS_NAMES.LAST_NAME_SRCH <> ' ' AND PS_SCC_STG_NAMES.LAST_NAME_SRCH <> ' ' AND PS_PERSON.EMPLID = PS_PERS_DATA_EFFDT.EMPLID AND PS_SCC_STG_PDE.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND PS_SCC_STG_CONSTIT.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND CANDS.PROCESS_INSTANCE = 4886856 AND PS_PERS_DATA_EFFDT.SEX = PS_SCC_STG_PDE.SEX AND PS_PERS_DATA_EFFDT.SEX <> ' ' AND PS_SCC_STG_PDE.SEX <> ' ' AND PS_PERSON.EMPLID = PS_PERSONAL_PHONE.EMPLID AND PS_SCC_STG_PHONE.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND PS_SCC_STG_CONSTIT.SCC_TEMP_ID = CANDS.SCC_TEMP_ID AND CANDS.PROCESS_INSTANCE = 4886856 AND PS_PERSONAL_PHONE.PHONE = PS_SCC_STG_PHONE.PHONE AND PS_PERSONAL_PHONE.PHONE <> ' ' AND PS_SCC_STG_PHONE.PHONE <> ' ' AND CANDS.PROCESS_INSTANCE = 4886856 AND NOT EXISTS ( SELECT 'X' FROM PS_SCC_SLSM3_TMP4 T3 WHERE T3.PROCESS_INSTANCE = 4886856 AND T3.SCC_TEMP_ID = CANDS.SCC_TEMP_ID ) GROUP BY CANDS.SCC_TEMP_ID , PS_PERSON.EMPLID

Process 4886856 ABENDED at Step SCC_SL_SMSET.DynRule.Step01 (SQL) -- RC = 1652 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s


Steps to reproduce the issue:

1. Run Transaction Management Process,  SCC_SL_SMSET
2. When process goes to No Success, click Details
3. View Message Log / Trace

Cause

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