Thursday, March 15, 2012

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

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

5 comments:

Vinit said...

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

Santosh Dhongade said...

Agreed, its just a guideline for people who face this error

rajeev said...

Oracle Apps R12 and Fusion Cloud Self Paced Training Videos by Industry Experts. Please Check https://www.oracleappstechnical.com

Smith said...

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.

Oracle Apps Technical Dot Com said...

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