Temp segment, info problem. 
Author Message
 Temp segment, info problem.

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



Tue, 14 Sep 2004 03:06:56 GMT
 Temp segment, info problem.

Oracle drops temporary segments once the statement that needed them
completes.
Quote:

> 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



Tue, 14 Sep 2004 05:00:20 GMT
 Temp segment, info problem.

Quote:

>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



Tue, 14 Sep 2004 05:06:54 GMT
 Temp segment, info problem.
Leonardo Camargo doodled thusly:

Quote:
>I have the following problem, or i am a crossed-eye.

It's not a problem (at least not as in "bug") and nothing wrong with
your eyes either.

When Oracle needs to use disk space as a result of a lengthy
operation, it creates the space as a temporary segment, which becomes
permanent at completion of the operation.

This is the case for example of creatuing an index.  The keys are
sorted and then written back in index blocks which are in temporary
segments until the last key has been written and the CREATE INDEX is
complete.  Then they are flagged as permanent segments.

If the CREATE INDEX fails, then the temporary segments are just that
and gone.

Probably what happened in your case.

Cheers
Nuno Souto



Tue, 14 Sep 2004 10:04:53 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

2. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

3. ORA-01652: unable to extend temp segment by 512 in tablespace TEMP

4. temp segment in non-temp tablespace??

5. 01652: unable to extend temp segment

6. ORA-1652: unable to extend temp segment by 7680 in tablespace

7. Some question about temp segment

8. Strange Segment called 6.2 in TEMP

9. Formula to calculate size of temp segment when analyzing tables/schemas

10. Question on Temp Segments

11. ORA-01652 unable to extend temp segment by num in tablespace name


 
Powered by phpBB® Forum Software