Post a Comment On: Oracle Help

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