My Oracle Support Banner

SCC_SL_SMSET Uses All of the 32GB PSTEMP Tablespace (Doc ID 2530245.1)

Last updated on DECEMBER 08, 2019

Applies to:

PeopleSoft Enterprise CS Campus Community - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Campus Community Other

PROBLEM
---------------
SCC_SL_SMSET process to import test scores used up 32GB of PSTEMP tablespace and the process stops. Breaking the file into multiple smaller files and the process will post successfully, but this is not an ideal workaround to get the correct end result.

ERROR:

---------------

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 809 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('AN'), MAX('N') FROM PS_SCC_SLSM4_TMP4 CANDS, PS_PERSON, PS_SCC_STG_CONSTIT, PS_NAMES, PS_SCC_STG_NAMES 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 = 1387837 AND SUBSTR(PS_NAMES.FIRST_NAME_SRCH,1,3) = SUBSTR(PS_SCC_STG_NAMES.FIRST_NAME_SRCH,1,3) AND PS_NAMES.FIRST_NAME_SRCH <> ' ' AND PS_SCC_STG_NAMES.FIRST_NAME_SRCH <> ' ' AND SUBSTR(PS_NAMES.LAST_NAME_SRCH,1,5) = 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 CANDS.PROCESS_INSTANCE = 1387837 AND NOT EXISTS ( SELECT 'X' FROM PS_SCC_SLSM3_TMP4 T3 WHERE T3.PROCESS_INSTANCE = 1387837 AND T3.SCC_TEMP_ID = CANDS.SCC_TEMP_ID ) GROUP BY CANDS.SCC_TEMP_ID , PS_PERSON.EMPLID

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



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run SCC_SL_SMSET with 32GB spacke on PSTEMP tablespace
2. Process will stop with the above error in the log.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run SCC_SL_SMSET without breaking the file into multiple smaller files.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


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