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

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



Tue, 15 Feb 2005 07:39:02 GMT
 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


Quote:
> 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



Tue, 15 Feb 2005 08:45:18 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. ORA-1652: unable to extend temp segment by 8192 in tablespace

2. ORA-1652: unable to extend temp segment

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

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

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

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

7. ORA-01652: unable to extend temp segment by 1598 in tablespace

8. ORA-01652: unable to extend temp segment by 1598 in tablespace

9. unexpected ORA-1652: alter index rebuild partition tablespace

10. 01652: unable to extend temp segment

11. Unable to extend temp segment...


 
Powered by phpBB® Forum Software