The error ORA-01652 : unable to extend temp segment by 128 in tablespace temp occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So maybe we could address this issue as following:
- Check the maxsize for TEMP tablespace. Probably TEMP's datafile had reached MAXSIZE.
Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace (i.e tablespace TEMP) indicated. - Check the memory value set for the sort area size. problem can be that the size of sort area is more than the extent size of the temp table space .eg if your sort area size is 128 and temp extent size is 64 it will generate the same error as if there is no space on temp tablespace or datafile for further extesion.
Here is a useful discussion link about this issue:
http://database.itags.org/oracle/168091/