Skip to Content
avatar image
Former Member

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

Hi All,

We are using BI 7.01 system installed on Oracle 10.2.0.4 and OS is HP-UNIX.

We have currently PSAPTEMP tablespace 0% used but still when performing some data loads we are getting error:

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

Please suggest possible reasons of this error.

Regards,

Shivam Mittal

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Mar 01, 2011 at 03:42 PM

    Hi,

    ORA-01652: This error is caused by a tablespace overflow. As a

    rule of thumb, PSAPTEMP should be at least twice as large as the

    largest index. If an overflow occurs despite this size, this is

    frequently due to external reasons such as incorrect Oracle

    parameter settings, large-scale parallel processing or

    unfavorable access paths. See also Note 3155.

    Also please refer following note:

    -> 825653 - Oracle: Common errors (Item 45 from the 'Solution' part)

    45. If the error "ORA-01652: unable to extend temp segment ...

    Regards

    Vivek

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 01, 2011 at 12:35 PM
    We have currently PSAPTEMP tablespace 0% used

    Then what is the total allocated space of PSAPTEMP tablespace?

    It seems very less space was allocated to your temp table space, just check and add a new datafile to your temp tablespace and re-run.

    *ORA-01652 is a common error, Search Google/SDN before posting the Oracle errors (Error itself showing the code)

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 01, 2011 at 01:12 PM

    That is why it is called PSAP TEMP

    The space is only used temporary, during sort operations and for example index creations. If you extend PSAPTEMP, you have a very good chance the error goes away.

    Cheers Michael

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 02, 2011 at 04:34 PM

    Hi Shivam,

    First you can check if you have any datafiles available in tablespace 'PSAPTEMP' using DB02 tcode. Even though you have a datafile, chances are there for getting this error if high temporary disk space is being used during BI loading.

    I would suggest you to go through the ST22 dumps generated during this error prompt which will give you clear picture of shortage of temp disk space. Once you analyze the space, you can extend the tablespace accordingly and get rid of tihs error.

    Regards,

    Ankit Gupta

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 02, 2011 at 04:51 PM

    where have you found thta PSAPTEMP tablespace was 0% used ?

    you cannot rely on DB02 for up-to-date information. this info only gets updated twice a day, usually at 7am and 7pm.

    go in BRTOOLS to get a real actual status.

    other than that, as this tablespace holds temporary objects, they might have been deleted after the error occurred.

    Add comment
    10|10000 characters needed characters exceeded