SES Schedule Search Index for HC_HR_JOB_DATA Ran to Error: "ORA-01652: Unable to Extend Temp Segment by 16 in Tablespace PSTEMP" (Doc ID 1919757.1)

Last updated on JULY 03, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When attempting to run the process Schedule Search Index for the search definition HC_HR_JOB_DATA, the following error occurs.

ERROR
-----------------------
File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 7072 Error Position: 0 Return: 1652 - ORA-01652: unable to extend temp segment by 16 in tablespace PSTEMP
Failed SQL stmt: SELECT A.EMPLID, A.EMPL_RCD, A.DEPTID, A.JOBCODE, A.POSITION_NBR, A.REG_TEMP, A.FULL_PART_TIME, A.BUSINESS_UNIT, A.PER_ORG, A.LOCATION, B.DESCR, C.DESCR, A.REG_REGION, E.DESCR, F.DESCR, G.NAME, G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, G.SECOND_LAST_NAME, G.NAME_AC, G.NAME_DISPLAY, CASE WHEN NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) THEN NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) WHEN NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) THEN NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) WHEN NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) THEN NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) WHEN NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) THEN NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) WHEN NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((F.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) AND NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) > NVL(TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) THEN NVL(TO_CHAR(CAST((J.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),TO_TIMESTAMP('1900-01-01-00.00.00.000000','YYYY-MM-DD-HH24.MI.SS.FF')) ELSE TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24.MI.SS.FF') END, 'psp/EMPLOYEE/HRMS/c/ADMINISTER_WORKFORCE_(GBL).JOB_DATA.GBL?Action=U&EMPLID=%A.EMPLID%&EMPL_RCD=%A.EMPL_RCD%', D.DESCR50, A.PAYGROUP, A.GP_PAYGROUP, A.PAY_SYSTEM_FLG, L.DESCR, N.DESCR, A.ABSENCE_SYSTEM_CD, P.XLATLONGNAME, A.ESTABID, R.HR_SRCH_ABS_STM_CD, R.XLATLONGNAME, A.COMPANY, S.DESCR, A.GP_PAYGROUP, A.PAYGROUP, L.DESCR, N.DESCR, A.PAY_SYSTEM_FLG, P.XLATLONGNAME, R.XLATLONGNAME, A.COMPANY, S.DESCR, G.LAST_NAME_SRCH, G.SECOND_LAST_SRCH, G.NAME_DISPLAY_SRCH, CASE WHEN NVL(A.BUSINESS_UNIT,'No Value') = ' ' THEN 'No Value' ELSE NVL(A.BUSINESS_UNIT,'No Value') END , CASE WHEN NVL(D.DESCR50,'No Value') = ' ' THEN 'No Value' ELSE NVL(D.DESCR50,'No Value') END , CASE WHEN NVL(E.DESCR,'No Value') = ' ' THEN 'No Value' ELSE NVL(E.DESCR,'No Value') END , CASE WHEN NVL(F.DESCR,'No Value') = ' ' THEN 'No Value' ELSE NVL(F.DESCR,'No Value') END , CASE WHEN NVL(C.DESCR,'No Value') = ' ' THEN 'No Value' ELSE NVL(C.DESCR,'No Value') END , TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM ((PS_HR_SRCH_JOB_VW A LEFT OUTER JOIN PS_HR_SRCH_POS_VW B ON B.POSITION_NBR = A.POSITION_NBR ) LEFT OUTER JOIN PS_LOC_TBL_EF_VW C ON C.SETID = A.SETID_LOCATION AND C.LOCATION = A.LOCATION ), PS_DEPT_TBL_VW E, PS_HR_SRCH_JOBCDVW F, PS_HR_SRCH_NAME_VW G, PS_REG_REGION_TBL D, (PS_HR_SRCH_JOB_VW I LEFT OUTER JOIN PS_HR_SRCH_NIDDTTM J ON I.EMPLID = J.EMPLID ), (PS_HR_SRCH_JOB_VW K LEFT OUTER JOIN PS_PAYGROUP_TBL L ON L.COMPANY = K.COMPANY AND L.PAYGROUP = K.PAYGROUP ), (PS_HR_SRCH_JOB_VW M LEFT OUTER JOIN PS_GP_PYGRP N ON N.GP_PAYGROUP = M.GP_PAYGROUP ), (PS_HR_SRCH_JOB_VW O LEFT OUTER JOIN PS_GP_SRCH_PAYSTM P ON P.PAY_SYSTEM_FLG = O.PAY_SYSTEM_FLG ), (PS_HR_SRCH_JOB_VW Q LEFT OUTER JOIN PS_HR_SRCH_ABS_STM R ON Q.ABSENCE_SYSTEM_CD = R.HR_SRCH_ABS_STM_CD ), PS_COMPANY_TBL S WHERE ( S.COMPANY = A.COMPANY AND ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_HR_SRCH_JOB_VW A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT = TO_DATE(TO_CHAR(TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR A.LASTUPDDTTM >= TO_TIMESTAMP(:2,'YYYY-MM-DD-HH24.MI.SS.FF') OR B.EFFDT >= TO_DATE(TO_CHAR(TO_TIMESTAMP(:3,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR B.LASTUPDDTTM >= TO_TIMESTAMP(:4,'YYYY-MM-DD-HH24.MI.SS.FF') OR C.EFFDT >= TO_DATE(TO_CHAR(TO_TIMESTAMP(:5,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR E.EFFDT >= TO_DATE(TO_CHAR(TO_TIMESTAMP(:6,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR F.EFFDT >= TO_DATE(TO_CHAR(TO_TIMESTAMP(:7,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR F.LASTUPDDTTM >= TO_TIMESTAMP(:8,'YYYY-MM-DD-HH24.MI.SS.FF') OR G.EFFDT >= TO_DATE(TO_CHAR(TO_TIMESTAMP(:9,'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD'),'YYYY-MM-DD') OR G.LASTUPDDTTM >= TO_TIMESTAMP(:10,'YYYY-MM-DD-HH24.MI.SS.FF')) AND D.REG_REGION = A.REG_REGION AND A.EMPLID = I.EMPLID AND A.EMPL_RCD = I.EMPL_RCD AND A.EFFSEQ = I.EFFSEQ AND A.EMPLID = K.EMPLID AND A.EMPL_RCD = K.EMPL_RCD AND ( L.EFFDT = (SELECT MAX(L_ED.EFFDT) FROM PS_PAYGROUP_TBL L_ED WHERE L.COMPANY = L_ED.COMPANY AND L.PAYGROUP = L_ED.PAYGROUP AND L_ED.EFFDT
Error in running query because of SQL Error, Code=1652, Message=ORA-01652: unable to extend temp segment by 16 in tablespace PSTEMP (50,380)

Internal Error: invalid transaction handle index passed from SamDestroy: 5216 vs 4



STEPS TO REPLICATE

1. Go to: Main Menu > PeopleTools > Search Framework > Administration > Schedule Search Index
2. Create a new Run Control
3. Use the Search Definition HC_HR_JOB_DATA
4. Run the process


Changes

 

Cause

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