ORA-01652 (Non RAC)

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

The space in the temporary tablespace is not enough to allocate for the temp segment.

A temp segment, in the temporary tablespace, is not always used for sorting. It is sometimes used for creating objects such as index, CTAS etc. At the end of the creation these temp segments are converted in to the real objects. Hence rebuilding/creating a large index requires large temp segments thereby resulting in ORA-01652.

Solution: Increase the size of the temp tablespace by using the statement

SQL> Alter tablespace add tempfile ‘path’ size ‘size’;

Thanks