Skip to Content
author's profile photo Former Member
Former Member

PSAPTEMP default storage (Parameter settings)

Hi there,

I am trying to increase INITIAL and NEXT values for default setting of PSAPTEMP. Using sapdba version 9, under Tablespace, when I select the INITIAL and change value, the value gets reset to default 1024K. How can I extend these values. Do I need to drop and recreate or can I do it online.



Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2007 at 09:02 AM

    hi Jeet

    Temperory tablespace is use for sorting purpose and it contain all temporary data

    so why can't you coalesce the tablespace so that the tablespace will release the fragment space

    For coalesce the tablepsace the command is

    alter tablespace PSAPTEMP coalesce;

    if than also your problem is not solving than please let me know



    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Jeet,

      Ok try to change on sql cmd prompt

      but htis thing sap wont recommended but than also you can do it forcefully

      otherwise you can drop that teblespace and create with new storage value

      but be alert u should do this activity in non peak hours i.e while user are not there

      and no database activity should be running at that time

      hope this will solve your problem



  • Posted on Jan 10, 2007 at 12:50 PM

    Dear Jeet,

    first of all: don't use sapdba anymore.

    It's not developed anymore and the recommendated tool is now br*tools.

    See note

    12741 - Current versions of SAPDBA and BR*Tools

    for details

    Concerning your question:

    When you've got a locally managed temp. tablespace (as it is recommended) then the INITIAL and NEXT values should be identical and the allocation type for the tablespace should be UNIFORM.

    The exact value for this parmeters depends on the sort_area-size used in the database.

    The formula looks like this:

    ( N * sort_area_size ) + 2 * db_block_size

    db_block_size is 8K in SAP systems and the N depends on the size of your PSAPTEMP (tempsize):

    N = 1 : tempsize <= 2 GB

    N = 3 : 2 GB < tempsize <= 6 GB

    N = 5 : 6 GB < tempsize <= 10 GB

    N = 10 : 10 GB < tempsize

    All this - in more detail - can be found in these notes:

    164925 - Storage parameter of tablespace PSAPTEMP

    659946 - FAQ: Temporary tablespaces

    Hope that helps.

    Best regards, Lars

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Jeet,

      actually the you should use brconnect right away anyhow.

      It's definitively working with your SAP/Oracle combination.

      Concerning your original question I just checked: it's not possible to alter the size of the UNIFORM allocation for the temp tablespace.

      To get a PSAPTEMP with the wanted extent management parameters you've to create a new one.

      As there is no permanent data in it this should not take to long to accomplish, since no reorg is necessary here.

      Just a drop and recreate of the temp tablespace.

      So neither SAPDBA nor BR*TOOLS can provide this option to simply alter the parameters.

      Best regards,


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.