This content has been marked as final. Show 11 replies
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
But then too, the DAC is returning the same tablespace issue like
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;
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1Im 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_USAGERegards,
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
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
But im wondering since i have tablespace of TEMP1 only but it is showing error with the TEMP2 tablespace which is not existing @ all.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
since in the TEMP1 tablespace have three temp files with in it, namely
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
temp1.dbf--->25gb temp2.dbf--->20gb temp3.dbf--->25gb
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.
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 worksBut i couldnt get you from the above line, whether r u asking to run that query in toad.
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.
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
So the DAC needs the space to be allocated in source(TEMP1 AND TEMP2).
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.
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.