ORA-1652: unable to extend temp segment

[Former Member]

Dear Gurus,

Our SAP CRM 5.0 system having a database size of 110 GB, running in Oracle 10g, AIX platform.

We are having frequent "ORA-1652: unable to extend temp segment" error in our alert log.

The temp tablespace PSAPTEMP is 21GB in size.

The largest index is 1.5 GB in size.

We have 4 temp datafiles which consumes the same size on disk (not sparse file).

Could you please help to check whether:

1. the current size of PSAPTEMP (21 GB) is enough or not

2. is the temp tablespace really used up or not.

3. "how" this will adversly effect the performance of the system.

Thanks in advance !

Regards,

Kiran

[lbreddemann]

HI there,

in many (if not most) of the cases, this error occurs because of a wrong optimizer decision that leads to huge intermediate result sets.

A merge join cartesian operation would be a common example for such an error.

To avoid this, make sure to have the latest SAP Bundle Patch installed and the parameters checked and corrected.

regards,

Lars

[volker_borowski2]

Hello,

TEMP is also used for storing intermediate results like SORTED query resultsets and likewise.

It is very easy to generate large resultsets by joining several tables.

You should ceck transaction ST22 to get the offending program that is causing this error.

Is this "your" program or a SAP standard program?

My be something should be done about it's design.

Volker