Re: ora-01630 question

On 26 Jun 2002 14:55:39 -0700, nilanjan_sarkar_at_hotmail.com (nilanjan) wrote:

>I got this error in an SAP instance with ORACLE database
>ORA-01630: max # extents (300) reached in temp segment in tablespace
>PSAPBTABI#
>
>There isnt a job abort log to find out what application code caused
>it. Now I would like to find out
>
>-- The tablespace mentioned is not a TEMP space for the Oracle user
>or neither it is TEMPORARY storage type tablespace . (It is a
>permanent tablespace typically used by SAP for Indexes !) So why is
>Oracle using it instead of the real temp tablespace?

Indexes are initially created as temporary segments. After creation is complete the name of the segment and the type are updated.

>
>-- Is the maxextents being setup by application code somewhere, or is
>it being picked up from TableSpace defaults ?
That's something SAP should answer. Apparently they are creating indexes on the fly. If they don't specify a maxextents the maxextents from the default storage clause of the tablespace is picked up. You can verify the default storage clause easily from dba_tablespaces. If the maxextents column shows 300 there is your answer.
>-- Lets assume the maxextents cannot/would not be changed at the
>Tablespace level . How can we resolve this ?

ALTER INDEX storage (maxextents ....) or is that too easy?
>
>Any clues ?

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jun 26 2002 - 17:55:27 CDT