Error:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Issue and its details:
Submit your concurrent Program and the Program ends up in error with log showing
the below error message
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Cause:
If you go by the error its actually caused when there is huge data being fetched by the Program/Query
and it packs up the allocated TEMP Tablespace. In this case there is nothing much can be done
by the developer and its DBA job.
But do few checks before actually contacting the DBA
1. Run the Program for different set of Parameters(which will fetch almost same amount of data) and it should
fail.
2. Run the query in Toad with same/different set of Parameters and you should get the same error.
If you don't get the same error in step 1 and 2 its not issue with TEMP space its something do
with your query/Program
Solution:
In case its DBA issue, contact DBA and ask him to perform following steps
1.Verify and modify the datafiles sizes
SELECT * FROM DBA_DATA_FILES;
2.Verify the user’s default tablespace to a bigger one
SELECT * FROM Dba_Users;
The issue in my case was not with the DBA,as I performed the Step 1 and 2 in Cause section
the error didn't occur.
Actually I was using a OPTIMIZER HINT and it was causing this issue, I removed the HINT and
the error disappeared. But I had hit on performance of my query by removing the HINT
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Issue and its details:
Submit your concurrent Program and the Program ends up in error with log showing
the below error message
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Cause:
If you go by the error its actually caused when there is huge data being fetched by the Program/Query
and it packs up the allocated TEMP Tablespace. In this case there is nothing much can be done
by the developer and its DBA job.
But do few checks before actually contacting the DBA
1. Run the Program for different set of Parameters(which will fetch almost same amount of data) and it should
fail.
2. Run the query in Toad with same/different set of Parameters and you should get the same error.
If you don't get the same error in step 1 and 2 its not issue with TEMP space its something do
with your query/Program
Solution:
In case its DBA issue, contact DBA and ask him to perform following steps
1.Verify and modify the datafiles sizes
SELECT * FROM DBA_DATA_FILES;
2.Verify the user’s default tablespace to a bigger one
SELECT * FROM Dba_Users;
The issue in my case was not with the DBA,as I performed the Step 1 and 2 in Cause section
the error didn't occur.
Actually I was using a OPTIMIZER HINT and it was causing this issue, I removed the HINT and
the error disappeared. But I had hit on performance of my query by removing the HINT
5 comments:
A DBA will not opt for increase the Tablespace. Will check on processes eatingup the TEMP space. Look for option to schedule the activities in a better way.
And only after going throught the option may go for increasing the temp space.....
Agreed, its just a guideline for people who face this error
Oracle Apps R12 and Fusion Cloud Self Paced Training Videos by Industry Experts. Please Check https://www.oracleappstechnical.com
Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students
Please Check https://www.oracleappstechnical.com for details
Post a Comment