size of PSAPTEMP

[Former Member]

When creating an index for an very large table I got ORA problem:

Database error 1652 at EXE

> ORA-01652: unable to extend temp segment by 128 in tablespac

> PSAPTEMP

Database error 1652 at EXE

PSAPTEMP size is 10GB and is used 0%. Is it used only during index creation?. If I need increase the size what should be the size PSAPTEMP

[Former Member · Accepted solution]

Hello Jan,

Refer to Note 825653

If the error "ORA-01652: unable to extend temp segment ... in tablespace PSAPTEMP" occurs, the table space PSAPTEMP must be increased.

In many cases, increasing PSAPTEMP is not a suitable means of avoiding ORA-01652 errors. Instead, you should always check whether the triggering SQL statement executes unnecessarily large sortings or hash operations that cause a high PSAPTEMP load. Only when the performance of the SQL statement is optimal and cannot be improved further, must PSAPTEMP be increased. See also Note 659946.

Let me know of this helps

Rohit

[Former Member]

Hi

Yes, there will only be data in the temp tablespace during the sort operation. So you will need approx. the size of the new index in your temp tablespace. I suggest you resize PSAPTEMP to the same size as the biggest already existing index of that table.

It is possible to shrink the tablespace afterwards if you need the diskspace back.

See also [659946 - FAQ: Temporary tablespaces|https://service.sap.com/sap/support/notes/659946]

Best regards, Michael