Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Sep 22, 2015 at 03:12 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Sep 22, 2015 at 05:29 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member S Sriram

      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.