This discussion is archived
11 Replies Latest reply: Oct 25, 2012 5:38 AM by Saro RSS

SDE_ORA_PayrollFact Error

Saro Explorer
Currently Being Moderated
Hi friends,

As a part of my HRMS load, i faced some failed task during the run. Soon after that, as adviced by the Expert Svee i tried to apply the patch p14306642_101341_Generic.zip and soon after that so many failed task issue is resolved expect the following failed task "SDE_ORA_PayrollFact".

When i checked with the session logs of that task it says like
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1
But im very sure, that i have enough space for all the users that i have created and allocated. Since i checked in toad too.
I couldnt analyze the cause behind the issue, since the error is occuring inspite of the available tablespace.

Hence for the datawarehouse i have allocated default index space as OBIEE_INDEX in which it has nearly 8gb of tablespace.

Kindly help me with this friends.

Regards,
Saro
  • 1. Re: SDE_ORA_PayrollFact Error
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You may need to add datafile or ask your dba to make it auto that may helps.
    Also check Troubleshooting [ID 793380.1]

    1) Check the datafiles sizes..
    SELECT * FROM DBA_DATA_FILES;

    2) Change the user’s default tablespace to a bigger one
    SELECT * FROM Dba_Users;

    If helps pls mark
  • 2. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Hi svee,

    From my issue, it said of tablespace related to TEMP(which is a temporary tablespace)

    I also tried to increased that temporary tablespace upto 8 gb with a below command
    ALTER DATABASE TEMPFILE '/ebiz/db/app/db/oradata/OBIDB/temp01.dbf' RESIZE 5120M;
    ALTER DATABASE TEMPFILE '/ebiz/db/app/db/oradata/OBIDB/temp01.dbf' AUTOEXTEND ON  NEXT 1M MAXSIZE UNLIMITED;
    But then too, the DAC is returning the same tablespace issue like
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1
    Im not sure, why inspite of available tablespace it is reporting tablespace issue.I think need to check with DBA

    From the below query i found out the issue is with TEMP tablespace
    select * from V$SORT_USAGE
    Regards,
    SAro
  • 3. Re: SDE_ORA_PayrollFact Error
    Ahsan Shah Expert
    Currently Being Moderated
    You need to allocate TEMP space based on the expected Data Volume of your DW (based on how much data is extracted from the source). Here is the settings that are recommended.

    Target Data Volume SMALL: Up to 400Gb --> 40 – 60Gb TEMP SPACE
    Target Data Volume MEDIUM: 400Gb to 2Tb --> 60-150GB TEMP SPACE
    Target Data Volume LARGE:2Tb and higher --> 150 – 250Gb TEMP SPACE


    Make sure to allocate this amount of TEMP space and rerun the load. TEMP space is needed for SQL processing and some of the queries require a significant amount of TEMP space.

    Pls mark correct
  • 4. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Hi Shah,

    Like you said i increased my TEMP1 tablespace to 70gb, but then too the "SDE_ORA_PayrollFact Error:" task is failing.

    If i look @ the log file means it is reporting error like below
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
    But im wondering since i have tablespace of TEMP1 only but it is showing error with the TEMP2 tablespace which is not existing @ all.

    since in the TEMP1 tablespace have three temp files with in it, namely
    temp1.dbf--->25gb
    temp2.dbf--->20gb
    temp3.dbf--->25gb
    Totally it has 70gb. Im not sure why it is reporting error with the tablespace TEMP2(that never exist in the db). I too confirmed with DBA's they also been under confusion of the reporting error in TEMP2 tablespace which never exist in the db

    What is the cause of this issue. Been fighting with this issue for 2 days in order to solve. Do you have any idea on it.

    Regards,
    Saro
  • 5. Re: SDE_ORA_PayrollFact Error
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Can you try to execute the SQ Source qualifier query in toad and try to traverse all records instead of just 500 and see how it works. That may helps...
  • 6. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Dear Svee,

    Thanks for the reply.

    this is the source qualifier query of the payroll Fact
    SELECT PAYROLL.ASSIGNMENT_ACTION_ID,
           PAYROLL.ASSIGNMENT_ID,
           PAYROLL.INPUT_CURRENCY_CODE,
           PAYROLL.OUTPUT_CURRENCY_CODE,
           PAYROLL.END_DATE,
           PAYROLL.START_DATE,
           PAYROLL.PAY_ADVICE_DATE,
           PAYROLL.LAST_UPDATE_DATE,
           PAYROLL.LAST_UPDATED_BY,
           PAYROLL.CREATED_BY,
           PAYROLL.CREATION_DATE,
           PAYROLL.INPUT_VALUE_ID,
           PAYROLL.RUN_RESULT_ID,
           PAYROLL.RESULT_VALUE,
           PAYROLL.ELEMENT_TYPE_ID,
           PAYROLL.SOURCE_TYPE,
           PAYROLL.LAST_UPDATE_DATE1,
           PAYROLL.LAST_UPDATE_DATE2,
           LKP.GRADE_ID,
           LKP.POSITION_ID,
           LKP.JOB_ID,
           LKP.LOCATION_ID,
           LKP.PERSON_ID,
           LKP.ORGANIZATION_ID,
           LKP.SET_OF_BOOKS_ID,
           LKP.ASSIGNMENT_STATUS_TYPE_ID,
           LKP.PERSON_TYPE_ID,
           LKP.PAY_BASIS_ID,
           LKP.EMPLOYMENT_CATEGORY,
           LKP.LABOUR_UNION_MEMBER_FLAG,
           LKP.BUSINESS_GROUP_ID,
           LKP.CHANGED_ON_DT,
           X_CUSTOM
      FROM (SELECT-- $$HINT1
                   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_RUN_RESULTS.SOURCE_TYPE,
                   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_PAYROLL_ACTIONS,
     PER_TIME_PERIODS,
     PAY_ASSIGNMENT_ACTIONS,
     PAY_RUN_RESULTS,
     PAY_RUN_RESULT_VALUES,
     PAY_ELEMENT_TYPES_F,
     PAY_INPUT_VALUES_F,
     PAY_ELEMENT_CLASSIFICATIONS 
             WHERE (PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >=
                          TO_DATE('$$LAST_EXTRACT_DATE','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', 'I', 'E')) PAYROLL,
           (SELECT-- $$HINT2
                   PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID ASSIGNMENT_ID,
                   PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE
                                                             EFFECTIVE_START_DATE,
                   PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE EFFECTIVE_END_DATE,
                   PER_ALL_ASSIGNMENTS_F.GRADE_ID GRADE_ID,
                   PER_ALL_ASSIGNMENTS_F.POSITION_ID POSITION_ID,
                   PER_ALL_ASSIGNMENTS_F.JOB_ID JOB_ID,
                   PER_ALL_ASSIGNMENTS_F.LOCATION_ID LOCATION_ID,
                   PER_ALL_ASSIGNMENTS_F.PERSON_ID PERSON_ID,
                   PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID ORGANIZATION_ID,
                   PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
                   PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID,
                   PER_ALL_PEOPLE_F.PERSON_TYPE_ID,
                   PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID,
                   PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY,
                   PER_ALL_ASSIGNMENTS_F.LABOUR_UNION_MEMBER_FLAG,
                   PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,
                   PER_ALL_ASSIGNMENTS_F.LAST_UPDATE_DATE AS CHANGED_ON_DT
              FROM PER_ALL_ASSIGNMENTS_F,
                   PER_ALL_PEOPLE_F
             WHERE PER_ALL_ASSIGNMENTS_F.PERSON_ID = PER_ALL_PEOPLE_F.PERSON_ID
               AND (       PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE <=
                                        PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE
                       AND PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE >=
                                          PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
                    OR     PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE <=
                                          PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
                       AND PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE >=
                                          PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
                   )) LKP
     WHERE LKP.ASSIGNMENT_ID(+) = PAYROLL.ASSIGNMENT_ID
       AND LKP.EFFECTIVE_START_DATE <= PAYROLL.END_DATE
       AND LKP.EFFECTIVE_END_DATE >= PAYROLL.END_DATE
    try to traverse all records instead of just 500 and see how it works
    But i couldnt get you from the above line, whether r u asking to run that query in toad.

    Regards,
    Saro
  • 7. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    I tried to run the query in toad means, it is taking too long to execute as the time went on more than 30 min.

    As of temporarily, i skipped that particular task and re-started the load in DAC svee.

    But still dont know the reason behind that failed task as of now.


    Regards,
    Saro
  • 8. Re: SDE_ORA_PayrollFact Error
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Try to apply the filter to the SQL so that query equals to full load query or initial extract date to it.
    During the query in toad run the query to know temp space, do this for each minute. Do this until you get some valid info.
  • 9. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    Thanks for your suggestion.

    Ill try now to run the query in toad and try with the ways like u said.

    But for the time being, i skipped that task. As of now the HRMS load is completed and somewhat managed to see the data for the HRMS in BI dashboard.

    Meanwhile, ill try to find the cause for that failed task like u said.

    Regards,
    Saro
  • 10. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Dear Svee,

    Just now i ran the query in toad(connecting to source) and I wondered the mistake is with my source.

    Because i can see two tablespaces(TEMP1 AND TEMP2). Like you said when i ran the query in toad it is throwing the error like
    ORA-01652
    
    unable to extend temp segment by string in tablespace string 
    
    Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
    
    Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
    So the DAC needs the space to be allocated in source(TEMP1 AND TEMP2).

    Without noticing that i checked in my target database where the TEMP1 and TEMP2 tablespace not even exist. Also i was very much confused with it so long.

    Really a smart move by you Expert(as you said to run the query in the source).

    As a action plan, ill increase the TEMP1 AND TEMP2 tablespace in the source and will again reload the HRMS data. Let you know once i completed the reload again svee.

    Thanks for your info.

    Regards,
    SAro
  • 11. Re: SDE_ORA_PayrollFact Error
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    I tried to increase the tablespace in source(ie in temp1) like u said and loaded and after that my SDE_ORA_PayrolFact task is completed successfully.

    Thanks for your help.

    Regards,
    Saro

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points