4 Replies Latest reply: Apr 30, 2010 1:38 AM by 636623 RSS

    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    NewObieeDeveloper
      We are using Oracle BI Apps 7.9.5 with Informatica 8.1.1 sp4 to extract data from EBS 11.5.10.

      Execution plan failed at task SDE_ORA_PayrollFact_Full with error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP while executing the following sql statement:

      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

      Severity: ERROR
      Timestamp: 4/7/2010 12:13:47 AM
      Node: node01_GCDWINHYD1GES04
      Thread: READER_1_1_1
      Process ID: 11024
      Message Code: RR_4035
      Message: SQL Error [
      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

      Database driver error...
      Function Name : Execute
      SQL Stmt : SELECT /*+ USE_HASH( PAY_RUN_RESULT_VALUES PAY_RUN_RESULTS PAY_INPUT_VALUES_F PAY_ASSIGNMENT_ACTIONS

      PAY_ELEMENT_TYPES_F PAY_PAYROLL_ACTIONS PAY_ELEMENT_CLASSIFICATIONS PER_TIME_PERIODS ) */
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
      PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
      PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
      PER_TIME_PERIODS.END_DATE,
      PER_TIME_PERIODS.START_DATE,
      PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
      PAY_PAYROLL_ACTIONS.CREATED_BY,
      PAY_PAYROLL_ACTIONS.CREATION_DATE,
      PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
      PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
      PAY_RUN_RESULT_VALUES.RESULT_VALUE,
      PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
      PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
      PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
      '0' AS X_CUSTOM
      FROM
      PAY_RUN_RESULT_VALUES,
      PAY_RUN_RESULTS,
      PAY_INPUT_VALUES_F,
      PAY_ASSIGNMENT_ACTIONS,
      PAY_ELEMENT_TYPES_F,
      PAY_PAYROLL_ACTIONS,
      PAY_ELEMENT_CLASSIFICATIONS,
      PER_TIME_PERIODS
      WHERE
      (
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >= TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
      )
      AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
      AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_INPUT_VALUES_F.UOM = 'M'
      AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID = PAY_RUN_RESULTS.RUN_RESULT_ID
      AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID = PAY_INPUT_VALUES_F.INPUT_VALUE_ID
      AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID = PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
      AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID = PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
      AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE AND

      PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE AND

      PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
      AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
      AND PER_TIME_PERIODS.TIME_PERIOD_ID = PAY_PAYROLL_ACTIONS.TIME_PERIOD_ID
      AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
      AND CLASSIFICATION_NAME NOT LIKE '%Information%'
      AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
      AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
      AND PAY_RUN_RESULTS.SOURCE_TYPE IN('I','E')

      UNION ALL

      SELECT /*+ ORDERED USE_HASH( PAY_RUN_RESULT_VALUES PAY_RUN_RESULTS PAY_INPUT_VALUES_F PAY_ASSIGNMENT_ACTIONS

      PAY_ELEMENT_TYPES_F PAY_PAYROLL_ACTIONS PAY_ELEMENT_CLASSIFICATIONS PER_TIME_PERIODS ) */
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
      PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
      PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
      PER_TIME_PERIODS.END_DATE,
      PER_TIME_PERIODS.START_DATE,
      PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
      PAY_PAYROLL_ACTIONS.CREATED_BY,
      PAY_PAYROLL_ACTIONS.CREATION_DATE,
      PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
      PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
      PAY_RUN_RESULT_VALUES.RESULT_VALUE,
      PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
      PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
      PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
      '0' AS X_CUSTOM
      FROM
      PAY_RUN_RESULT_VALUES,
      PAY_RUN_RESULTS,
      PAY_INPUT_VALUES_F,
      PAY_ASSIGNMENT_ACTIONS,
      PAY_ELEMENT_TYPES_F,
      PAY_PAYROLL_ACTIONS,
      PAY_ELEMENT_CLASSIFICATIONS,
      PER_TIME_PERIODS
      WHERE
      (
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >= TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
      )
      AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
      AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_INPUT_VALUES_F.UOM = 'M'
      AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID = PAY_RUN_RESULTS.RUN_RESULT_ID
      AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID = PAY_INPUT_VALUES_F.INPUT_VALUE_ID
      AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID = PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
      AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID = PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
      AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE AND

      PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE AND

      PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
      AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
      AND PAY_PAYROLL_ACTIONS.DATE_EARNED BETWEEN PER_TIME_PERIODS.START_DATE AND PER_TIME_PERIODS.END_DATE
      AND PER_TIME_PERIODS.PAYROLL_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ID
      AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
      AND CLASSIFICATION_NAME NOT LIKE '%Information%'
      AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
      AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
      AND PAY_RUN_RESULTS.SOURCE_TYPE IN('V','R')
      Oracle Fatal Error
      Database driver error...
      Function Name : Execute
      SQL Stmt : SELECT /*+ USE_HASH( PAY_RUN_RESULT_VALUES PAY_RUN_RESULTS PAY_INPUT_VALUES_F PAY_ASSIGNMENT_ACTIONS

      PAY_ELEMENT_TYPES_F PAY_PAYROLL_ACTIONS PAY_ELEMENT_CLASSIFICATIONS PER_TIME_PERIODS ) */
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
      PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
      PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
      PER_TIME_PERIODS.END_DATE,
      PER_TIME_PERIODS.START_DATE,
      PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
      PAY_PAYROLL_ACTIONS.CREATED_BY,
      PAY_PAYROLL_ACTIONS.CREATION_DATE,
      PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
      PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
      PAY_RUN_RESULT_VALUES.RESULT_VALUE,
      PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
      PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
      PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
      '0' AS X_CUSTOM
      FROM
      PAY_RUN_RESULT_VALUES,
      PAY_RUN_RESULTS,
      PAY_INPUT_VALUES_F,
      PAY_ASSIGNMENT_ACTIONS,
      PAY_ELEMENT_TYPES_F,
      PAY_PAYROLL_ACTIONS,
      PAY_ELEMENT_CLASSIFICATIONS,
      PER_TIME_PERIODS
      WHERE
      (
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >= TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
      )
      AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
      AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_INPUT_VALUES_F.UOM = 'M'
      AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID = PAY_RUN_RESULTS.RUN_RESULT_ID
      AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID = PAY_INPUT_VALUES_F.INPUT_VALUE_ID
      AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID = PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
      AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID = PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
      AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE AND

      PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE AND

      PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
      AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
      AND PER_TIME_PERIODS.TIME_PERIOD_ID = PAY_PAYROLL_ACTIONS.TIME_PERIOD_ID
      AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
      AND CLASSIFICATION_NAME NOT LIKE '%Information%'
      AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
      AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
      AND PAY_RUN_RESULTS.SOURCE_TYPE IN('I','E')

      UNION ALL

      SELECT /*+ ORDERED USE_HASH( PAY_RUN_RESULT_VALUES PAY_RUN_RESULTS PAY_INPUT_VALUES_F PAY_ASSIGNMENT_ACTIONS

      PAY_ELEMENT_TYPES_F PAY_PAYROLL_ACTIONS PAY_ELEMENT_CLASSIFICATIONS PER_TIME_PERIODS ) */
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
      PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
      PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
      PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
      PER_TIME_PERIODS.END_DATE,
      PER_TIME_PERIODS.START_DATE,
      PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
      PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
      PAY_PAYROLL_ACTIONS.CREATED_BY,
      PAY_PAYROLL_ACTIONS.CREATION_DATE,
      PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
      PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
      PAY_RUN_RESULT_VALUES.RESULT_VALUE,
      PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
      PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
      PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
      '0' AS X_CUSTOM
      FROM
      PAY_RUN_RESULT_VALUES,
      PAY_RUN_RESULTS,
      PAY_INPUT_VALUES_F,
      PAY_ASSIGNMENT_ACTIONS,
      PAY_ELEMENT_TYPES_F,
      PAY_PAYROLL_ACTIONS,
      PAY_ELEMENT_CLASSIFICATIONS,
      PER_TIME_PERIODS
      WHERE
      (
      PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >= TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
      )
      AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
      AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
      AND PAY_INPUT_VALUES_F.UOM = 'M'
      AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID = PAY_RUN_RESULTS.RUN_RESULT_ID
      AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID = PAY_INPUT_VALUES_F.INPUT_VALUE_ID
      AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID = PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
      AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID = PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
      AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE AND

      PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
      AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE AND

      PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
      AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
      AND PAY_PAYROLL_ACTIONS.DATE_EARNED BETWEEN PER_TIME_PERIODS.START_DATE AND PER_TIME_PERIODS.END_DATE
      AND PER_TIME_PERIODS.PAYROLL_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ID
      AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
      AND CLASSIFICATION_NAME NOT LIKE '%Information%'
      AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
      AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
      AND PAY_RUN_RESULTS.SOURCE_TYPE IN('V','R')
      Oracle Fatal Error].

      Or How to determine how much TEMP tablespace the process needs and how to increase the TEMP tablespace?

      Thanks in Advance
      Sunil.