PSAPTEMP default storage (Parameter settings)

[Former Member]

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.

Regards,

Jeet

[lbreddemann]

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

[Former Member]

Hi Lars,

Thanks for your answer. I have already visited those notes and have already calculated the required size. The version of R/3 is 470 and Oracle 9 so we are still using SAPDBA until we upgrade later this year.

The issue I have is how to change the default values which is 1024K for both INITIAL and NEXT to the calculated value. Using SAPDBA and option Tablespace Administration, there is a option to select these values and change but whenever I do that, the value defaults back to 1024K. Do I have to use a sqlplus command or do I need to drop and recreate the tablespace with new values for INITIAL and NEXT.

Thanks,

Jeet

[lbreddemann]

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,

Lars

[Former Member]

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

cheer

Paresh

[Former Member]

As part of the EWA, SAP recomended to increase the size of PSAPTEMP (INITAIL and NEXT) values using the forlmula in OSS note 164925. I am having issues changing these values.

Regards,

Jeet

[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

Cheers

Paresh