ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP - how to track down the program that is causing the overflow

[behlau_carlos]

Hello SAP experts,

we are getting from time to time:

ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP

How can we track down the program that is causing the overflow?

As there is no run time error (ST22) nor sys log (SM21), we are wondering how to identify the program that is causing it?

The alert we are getting via db-check, scheduled via DB13.

When I check out DB16, I just see:

Tue Sep 22 07:48:18 2015

ORA-1652: unable to extend temp segment by 128 in tablespace                 PSAPTEMP

When I check the trace files (of Oracle Alert Log Directory), I am not finding any useful information, concerning identification ...

Thanks for your help and hint.

Best regards

Carlos Behlau

[Sriram2009]

Hi Carlos,

How can we track down the program that is causing the overflow?

Are you getting any runtime error " DBIF_RSQL_SQL_ERROR"  in ST22? in that you can find the transaction code. You can refer the SAP note 3155 already mention by Biborka.

Regards

SS

[behlau_carlos]

Hello Sriram,

thanks a lot for your help.

As there is no runtime error, I guess the issue is not located in ABAP. As the affected system is a SAP SolutionManager and the system is setup as dual stack system, running on a single host, with the same db, I guess I need to check more in detail the JAVA side via NWA.

Best regards

Carlos Behlau

[Sriram2009]

Hi Behlau

I think you have to fix the issue at DB level, could you check this links with same error message.

ORA-1652: unable to extend temp segment tips

BR

SS

[Former Member]

Hi Carlos,

You can find the pattern of when TEMP gets full.

Also check if you have jobs running in SAP or some kind of rerog/index build activity at DB level to see those are filling it.

Below is the snippet from (3155 - Termination due to tablespace overflow )

In Oracle 10.2.0.2 and 10.2.0. 4, bug 7716219 may also be responsible for an increased PSAPTEMP consumption in connection with Hash operations. Ensure that fix 7716219 is implemented or use the following parameter as a temproary workaround:_GBY_HASH_AGGREGATION_ENABLED =
FALSE

If, in the case of users other than SYS and SYSTEM, an ORA-01652 occurs with regard to the SYSTEM tablespace, this can be caused by the fact that the SYSTEM tablespace was, by mistake, allocated to a user as temporary tablespace. If the statement

SELECT USERNAME FROM DBA_USERS WHERE TEMPORARY_TABLESPACE = 'SYSTEM' AND USERNAME NOT LIKE 'SYS%';

returns the SAP or OPS$ user, the temporary tablespace should be
changed:

ALTER USER <username> TEMPORARY TABLESPACE
<psaptemp>;

where <psaptemp> is the temporary tablespace used
(such as PSAPTEMP).

As an workaround try to extend TEMP tablespace a little to see if the errors stop from appearing.

Regards,

Prithviraj.

[former_member207186]

Hi,

Regarding this issue/question, you can check size of PSAPTEMP and whether there were any large-scale parallel processing at the time of ORA-01652 occurence.

You can also refer to the SAP Notes below:

659946 - "FAQ: Temporary tablespaces" (especially point 7 and 10)

3155 - "Termination due to tablespace overflow"

766349 - "FAQ: Oracle SQL optimization"

Regards,

Bíborka

[behlau_carlos]

Hello Biborka,

thanks a lot for your help.

The tablespace PSAPTEMP (twice 30 GB, with autoextend=NO/OFF) is not having any limits, as far as I am able to judge. When I check the tablespace history I don't see it running out of space.

Is there a way, to enable to history check, that monitors tablespaces TEMP like, t-codes (ST03n)?

In DB02, I see only the history based on day, but not on hours/minutes ...

The affected system is a SAP SolutionManager and my guess is, the issue is not based on ABAP (as I am not seeing any runtime error or system log errors recorded). I just see from Oracle Alert Log, reported via DB-Check task the error report.

As SAP SolutionManager is also using JAVA Server, I am going to cross check the JAVA Instance, if here is something reported.

As the system is installed as dual stack on a single host, I forgot to check this side ...

Best regards

Carlos Behlau