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

Hi Maria,

Not sure what your level of experience is so if I state anything that you may define as the bleeding obvious, please forgive me.

Basically, your TEMP3 tablespace is not configured to be large enough to cope with the amount of simultaneous sorts that's required of it. The solution is to increase the size of the TEMP3 tablespace. The reason why sometimes it's sufficient and sometimes it's not is that load on the tablespace may differ at different times.

If you're unsure what value to size the tablespace, you could consider setting some of the tempfiles to autoextend and let Oracle tell you how much it needs (and then turn off and reconfigure as appropriate once determined).

Other things to check is if any other temp tablespaces could be better utilised and users assigned to TEMP3 assigned elsewhere. Also check that TEMP3 really is a temporary (rather than a permanent) tablespace.

The view V$TEMP_SPACE_HEADER can be used to determine how much free space you have left in your files. OEM has an event system that can be configured to alert you if your temporary tablespaces are getting full.

Cheers

Richard

 "Maria" <evoradba_at_yahoo.ca> wrote in message news:351fd9d6.0208291539.4a3932b4_at_posting.google.com...
> Hi there
> I have a oracle product server versiotn 8170 running on sun solori,
> also we have a fail over server with the same configuration. We have
> scripts that run on the database on a nightly basis and every so often
> we gat the following error messages:
>
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:06:46 2002
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:06:48 2002
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:13:24 2002
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:13:25 2002
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:13:26 2002
> ORA-1652: unable to extend temp segment by 7680 in tablespace TEMP3
> Tue Aug 27 01:13:27 2002
> Sometimes we don't get any error's at all, and they are the same
> scripts over and over
> after did we recycle Oracle did we regain the space that was taken up
> on Sunday night? Doesn't Oracle deal with this automatically
> sometimes we run out of space on TEMP tablespace
> , we had to cancel a ctas (rom primary to secondary )
> because it was delaying the nightly jobs. We killed the job while it
> was doing the update stats portion of the script but I have a feeling
> that it also corrupted the Oracle optimzer and cause the server
> environment
> to take of all the free memory swap and cause hight I/O and low CPU.
>
> Why after did we recycle Oracle did we regain the space that was
> taken
> How do we setup monitoring jobs for free space thresholds
>
> Any help would be great
> Thanks
> Lena
Received on Thu Aug 29 2002 - 19:45:18 CDT