ORA-01652: unable to extend temp segment

[CENSUSDBA]

Any suggestion on how to resolve this issue. I have tried several options:

1. COALESCED the tablespace
2. Added an additional datafile
3. Extended the maxextents to unlimited
4. and added more space to default storage.

Any assistance is appreciated.

HERE's the query thats causing the error:

select a.*
from sys.aud$ a, ops$oracle.db_lastsid b
where a.timestamp# between b.last_time and b.this_time;

[slimdave]

what's your explain plan?

[marist89]

ORA-01652: unable to extend temp segment

In what tablespace?

[gandolf989]

ORA-01652: unable to extend temp segment

In what tablespace?

Jeff, are you suggesting that he is working on the wrong tablespaces??? Of course he would have already looked at the alert log top see if there are any error messages there.

[CENSUSDBA]

It is the TEMP tablespace.


Here is the plan table:

OPERATION OPTIONS
SELECT STATEMENT
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL
FILTER
SORT JOIN
TABLE ACCESS FULL

[gandolf989]

It is the TEMP tablespace.


Here is the plan table:

OPERATION	               OPTIONS
SELECT STATEMENT	
MERGE JOIN	
SORT	                          JOIN
TABLE ACCESS             	FULL
FILTER	
SORT	                           JOIN
TABLE ACCESS               	FULL

Perhaps you should look at your disk volumes and see if any of them are nearly full, look at the max size of all of the datafiles and see if any of the datafiles are near their maxsize. This might not be an issue with the temp tablespace.