Re: Temp segment, info problem.

On 28 Mar 2002 11:06:56 -0800, leonardo.camargo_at_eds.com (Leonardo Camargo) wrote:

>Hi all,
>
>I have the following problem, or i am a crossed-eye.
>
>In my alert log file i have :
>Thu Mar 28 14:50:01 2002
>ORA-1652: unable to extend temp segment by 32768 in tablespace
> SYSTEM
>
>But when i sum all bytes in dba_free_space for system TS i get :
>TABLESPACE_NAME SUM(BYTES)
>------------------------------ ----------
>SYSTEM 635076608
>
>
>But i dont have a temp segment in system ts:
>SQL> select distinct segment_type from dba_segments where
>tablespace_name = 'SYSTEM' ;
>SEGMENT_TYPE
>------------------
>CACHE
>CLUSTER
>INDEX
>LOBINDEX
>LOBSEGMENT
>ROLLBACK
>
>or am i doing something too wrong ?
>
>Thanks in advance.
>
>Leo

Your program created a temporary segment in SYSTEM (which is *BAD*). Because it couldn't find 32k contiguous free space (that is what the error message is saying), the creation failed and your temporary segment was automatically dropped.
That is why you now don't see a temporary segment Temporary segment always derive their storage clause from the default storage clause of the tablespace.
It looks like

- you have a very small initial and next on system
- you have pctincrease 50 on system
- the free space is completely scattered

You need to investigate why your program creates a temporary segment in system (could be the temporary tablespace of the user) and to make sure it is located somewhere else.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Mar 28 2002 - 15:06:54 CST