Oracle Error ORA-01652

[saw15]

I continue to get the following error:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.

Our DBA has made considerable additions to adding tablespace to temp to no avail.

Are there any scripts that I can use to measure the tablespace for temp when the sql is running? Any other ideas as to what could be causing this?

Thanks

 

[DBAwhosaysNIE]

how "big" is the statement it's hacking on? are you sorting 10M rows on 8 keys or creating an index on 6 varchar2(4000) columns on a 10M row table?

how big are the tempfiles in the TEMP tablespace?

1 act of despiration which I use with EXTREME caution would be to turn autoextend on for the datafile(s) and CLOSELY watch the filesystem(s)

more info might help avoid such an extreme measure though

 

[jimbopalmer]

this would be very bad, but if your temp tablespace was made with a non zero percent increase, the next extent could be VERY large, compared to previous extents, also if the extent size is very small you could have hit max extent, often 505 extents I tried to remain child-like, all I acheived was childish.

 

[JJSmith]

Your problem would require you to supply much more detail for someone else to resolve it - however in answer to your question of monitoring the growth of temporary segments then:
As a user with access to DBA views, use the query;

SELECT segment_name,extents,bytes
FROM dba_segments
WHERE segment_type='TEMPORARY';

repeat this while your sql is in progress.

I would recommend that you do not take any action suggested until all the facts are know. Using autoextend is extremely hazardous to a systems health - If your DBA understands the implications of that, then he/she should know how to track temporary segments!!! - nuff said

Hope this helps

Myself and a number of others could provide a solution but the effort required by both parties to gather the facts would take a considerable amount of time.

JJSmith