ORA-01652: unable to extend temp segment

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;

what's your explain plan?

Code:

---

ORA-01652: unable to extend temp segment

---

In what tablespace?

Quote:

---

Originally Posted by marist89

Code:

---

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. :rolleyes:

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

Quote:

---

Originally Posted by CENSUSDBA

It is the TEMP tablespace.


Here is the plan table:

Code:

---

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

---

---

Code:

---

---

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.