Re: ORA-01630: altough I have enough space in temp tablespace

marcel.kraupp_at_gmx.ch (Marcel Kraupp) wrote in message news:<332bb004.0207160002.36db5bc9_at_posting.google.com>...
> Hello
>
> I am getting a
> ORA-max # extents (1017) reached in temp segment in tablespace SYSTEM
> when I run a procedure, that basically looks like
>
> create or replace abc
> ..
> begin
> for i in (select x, y, z from a, b, c where .... order by x, y, z) loop
> ..
> end loop;
> end;
>
>
> The procedure's owner is the same user that runs it, and that user
> has been assigned a temporary tablespace. This tablespace has plenty
> of space. Still, Oracle chooses to fill a temporary segment in the
> SYSTEM tablespace, or at least, that's what I understand from the
> error message.
>
> Is there a way to circumvent this problem as I don't want to grow the
> System TS?
>
> TIA
>
> MK

The error message is talking about the *number of extents* which has *nothing* to do with the amount of available space. As you don't post the full code it is impossible to see why a temp segment is created in SYSTEM. When you post code you should leave the group to decide where the problem is.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Tue Jul 16 2002 - 15:17:06 CEST