11 Replies Latest reply: Oct 25, 2012 7:38 AM by Saro RSS

    SDE_ORA_PayrollFact Error

    Saro
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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