Maxextent of PSAPTEMP = 0 : normal ?

[Former Member]

Hello,

SAP release ; ECC6

Oracle release : Oracle 10.2

Unix : Solaris 10

We had the following error this morning while trying to reorganize an index :

<b>ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP</b>

I was about to add new datafile to the tableblace PSAPTEMP but I noticed strange parameters related to this tablespace :

Tablespace Init ext.(Kb) Next ext.(Kb) Min ext. Max ext. Pct-Inc.

PSAPSR3 64 0 1 Unlimited 0

PSAPSR3700 64 0 1 Unlimited 0

PSAPSR3DB 64 0 1 Unlimited 0

PSAPSR3USR 64 0 1 Unlimited 0

<u>PSAPTEMP 1.024 1.024 1 0 0</u>

PSAPUNDO 64 0 1 Unlimited 0

SYSAUX 64 0 1 Unlimited 0

SYSTEM 64 0 1 Unlimited 0

Tablespace Size (kb) Free (kb) Used (%)Tab/ind Extents

PSAPTEMP 2.324.480 2.324.480 0 0 0

The Tablespace is empty.

The Maxextent of tablespace PSAPTEMP is null, in all our ECC6 environnements,

and has never been modified. I wanted to change it to "unlimited", I've searched in BRGUI, but I couln't find where i can do that. I presume it can be done using an alter command ?

So my questions are :

1/ Is it a "normal" setting in Oracle 10.2 to have the maxextent of PSAPTEMP = 0

2/ If not, how can I change, the maxextent size of PSAPTEMP ?

Thank you for your attention.

Any useful help would be highly appreciated.

Best Regards.

[Former Member · Accepted solution]

Hi Indus

<i>1/ Is it a "normal" setting in Oracle 10.2 to have the maxextent of PSAPTEMP = 0</i>

Yes, this is ok, temp tablespaces are 'special'

<i>2/ If not, how can I change, the maxextent size of PSAPTEMP ?</i>

You don't need to change the maxextent size, in your case the tablespace seems to be too small. Is the index larger than the temp tablespace? You most probably should increase your temp tablespace.

Best regards

Michaeal

[stefan_koehler · Accepted solution]

Hello Michael,

>> Yes, this is ok, temp tablespaces are 'special'

What?

If the maxextent is reached you will get the error from above... or what do you mean - what should be special? Do you mean the next extent and the uniform thing?

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan

I owe you a clarification, sorry for the late response but was away during christmas.

with "special" i meant:

- temp tablespaces have tempfiles instead of datafiles, they never hold data, they don't need to be backup-ed / recovered. So, for example, you cannot create a table in a temp tablespace:

SQL> create table mytab ( x number ) tablespace PSAPTEMP;

create table mytab ( x number ) tablespace PSAPTEMP

*

ERROR at line 1:

ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

- temp tablespaces have - as you mentioned - uniform extent allocation which is probably different to your data tablespaces where you have system managed extent allocation.

Regards Michael

[stefan_koehler · Accepted solution]

Hello Michael,

>> temp tablespaces have tempfiles instead of datafiles, they never hold data, they don't need to be backup-ed / recovered

Not up to 100%

In previous releases it was possible to make a temporary tablespace as a type of permanent. But you are right.. if you create the tablespace as temporary.. you are not able to add some datafiles to it.

But now i have read the question of the topic creator again.. and i have to say that i have read out the first time.

>> 1/ Is it a "normal" setting in Oracle 10.2 to have the maxextent of PSAPTEMP = 0

Yes this is normal and described in metalinknote #108971.1

>This behavior is documented. MAXEXTENTS is ignored for temporary tablespaces. As stated in the SQL Reference guide, only NEXT is noted for temporary tablespaces.

Regards

Stefan