# When using where clause like % error is given ORA-01652: unable to
 extend temp segment by 128 in tablespace TEMP

**URL:** <https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [September 7, 2010, 3:48pm UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801 "2010-09-07T15:48:39Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 7, 2010, 3:48pm UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/1 "2010-09-07T15:48:39Z")

</div>

Hi,  
I am running following SQL and getting error ORA-01652: unable to extend temp  
segment by 128 in tablespace TEMP.  
One thing I know is that when join with like  
(CRM\_Event\_Legacy.Legacy\_SFDC\_Opportunity\_ID(+) LIKE  
SFDC\_RawInquiry\_Stg.EP\_Event\_ID || ‘%’) was added then it gave this error.  
Before it was equijoin (CRM\_Event\_Legacy.Legacy\_SFDC\_Opportunity\_ID(+) =  
SFDC\_RawInquiry\_Stg.EP\_Event\_ID) and worked fine. I tried to use hints, but did  
not work either. Any suggestions?  
Thank you in advance

SELECT /\*+ use\_hash(SFDC\_RAWINQUIRY\_STG) use\_hash(CRM\_OPPORTUNITY)  
use\_hash(CRM\_PROSPECT) use\_hash(CRM\_EVENT\_LEGACY) _/  
SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_ID, SFDC\_RAWINQUIRY\_STG.LEAD\_ID,  
SFDC\_RAWINQUIRY\_STG.BRAND, SFDC\_RAWINQUIRY\_STG.GRAD\_LEVEL,  
SFDC\_RAWINQUIRY\_STG.CHANNEL, SFDC\_RAWINQUIRY\_STG.VENDOR\_CODE,  
SFDC\_RAWINQUIRY\_STG.VENDOR\_TIMESTAMP,  
SFDC\_RAWINQUIRY\_STG.VENDOR\_TRACKING\_NUMBER, SFDC\_RAWINQUIRY\_STG.ADDRESS\_STREET,  
SFDC\_RAWINQUIRY\_STG.ADDRESS\_CITY, SFDC\_RAWINQUIRY\_STG.ADDRESS\_STATE,  
SFDC\_RAWINQUIRY\_STG.ADDRESS\_POSTAL\_CODE, SFDC\_RAWINQUIRY\_STG.ADDRESS\_COUNTRY,  
SFDC\_RAWINQUIRY\_STG.EMAIL, SFDC\_RAWINQUIRY\_STG.PHONE,  
SFDC\_RAWINQUIRY\_STG.EP\_PRESENTER\_DSI, SFDC\_RAWINQUIRY\_STG.REP\_DSI,  
SFDC\_RAWINQUIRY\_STG.LEGACY\_SFID, SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_DELETED,  
SFDC\_RAWINQUIRY\_STG.SFDC\_INQUIRY\_CREATE\_DATE, SFDC\_RAWINQUIRY\_STG.OWNER\_ID,  
SFDC\_RAWINQUIRY\_STG.SFDC\_INQUIRY\_UPDATE\_DATE,  
SFDC\_RAWINQUIRY\_STG.PROGRAM\_OF\_INTEREST,  
SFDC\_RAWINQUIRY\_STG.LOCATION\_PREFERENCE, SFDC\_RAWINQUIRY\_STG.INQUIRY\_STATUS,  
SFDC\_RAWINQUIRY\_STG.PROSPECT\_TYPE, SFDC\_RAWINQUIRY\_STG.EP\_SCORE\_EXPLICIT,  
SFDC\_RAWINQUIRY\_STG.EP\_SCORE\_IMPLICIT, SFDC\_RAWINQUIRY\_STG.CAMPAIGN\_ID,  
SFDC\_RAWINQUIRY\_STG.DUPLICATE, SFDC\_RAWINQUIRY\_STG.GRADUATION\_DATE,  
SFDC\_RAWINQUIRY\_STG.GRADUATION\_TYPE, SFDC\_RAWINQUIRY\_STG.ORIGINAL\_LOCATION\_CD,  
SFDC\_RAWINQUIRY\_STG.INQUIRY\_SCORE, SFDC\_RAWINQUIRY\_STG.ELOQUA\_TIMESTAMP,  
SFDC\_RAWINQUIRY\_STG.EQUIFAX\_TIMESTAMP, SFDC\_RAWINQUIRY\_STG.ASSIGN\_KCCL,  
SFDC\_RAWINQUIRY\_STG.ASSIGN\_MILITARY, SFDC\_RAWINQUIRY\_STG.LEAD\_STATUS,  
SFDC\_RAWINQUIRY\_STG.LEAD\_CREATE\_DATE, SFDC\_RAWINQUIRY\_STG.LEAD\_DELETED,  
SFDC\_RAWINQUIRY\_STG.LEAD\_MASTER\_RECORD\_ID, SFDC\_RAWINQUIRY\_STG.LEGACY\_LEAD\_ID,  
SFDC\_RAWINQUIRY\_STG.ASSIGN\_INTERNATIONAL,  
SFDC\_RAWINQUIRY\_STG.CONTACT\_ID, SFDC\_RAWINQUIRY\_STG.EQUIFAX\_TRANSITION\_ID,  
SFDC\_RAWINQUIRY\_STG.EP\_EVENT\_ID,  
CRM\_OPPORTUNITY.CRM\_OPPORTUNITY\_ID, CRM\_OPPORTUNITY.CRM\_PROSPECT\_ID,  
CRM\_OPPORTUNITY.CRM\_CAMPAIGN\_ID, CRM\_OPPORTUNITY.CRM\_OWNER\_USER\_ID,  
CRM\_OPPORTUNITY.CRM\_ORIGINAL\_USER\_ID, CRM\_OPPORTUNITY.STAGE,  
CRM\_OPPORTUNITY.BRAND, CRM\_OPPORTUNITY.GRAD\_LEVEL, CRM\_OPPORTUNITY.CHANNEL,  
CRM\_OPPORTUNITY.VENDOR\_CODE, CRM\_OPPORTUNITY.VENDOR\_TIMESTAMP,  
CRM\_OPPORTUNITY.VENDOR\_TRACKING\_NUMBER, CRM\_OPPORTUNITY.LOCATION\_PREFERENCE,  
CRM\_OPPORTUNITY.EP\_SCORE\_EXPLICIT, CRM\_OPPORTUNITY.EP\_SCORE\_IMPLICIT,  
CRM\_OPPORTUNITY.LEAD\_STATUS, CRM\_OPPORTUNITY.ADVISOR\_DSI,  
CRM\_OPPORTUNITY.EP\_PRESENTER\_DSI, CRM\_OPPORTUNITY.REP\_DSI,  
CRM\_OPPORTUNITY.LEGACY\_SFID, CRM\_OPPORTUNITY.CLOSED\_REASON,  
CRM\_OPPORTUNITY.CLOSED\_DETAIL, CRM\_OPPORTUNITY.APPLICATION\_DATE,  
CRM\_OPPORTUNITY.REGISTRATION\_DATE, CRM\_OPPORTUNITY.ATTENDED\_DATE,  
CRM\_OPPORTUNITY.SFDC\_RAW\_INQUIRY\_ID, CRM\_OPPORTUNITY.SFDC\_LEAD\_ID,  
CRM\_OPPORTUNITY.SFDC\_OPPORTUNITY\_ID, CRM\_OPPORTUNITY.DW\_ORGINAL\_LOCATION\_ID,  
CRM\_OPPORTUNITY.ADDRESS\_STREET\_INQUIRY, CRM\_OPPORTUNITY.ADDRESS\_CITY\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_STATE\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_POSTAL\_CODE\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_COUNTRY\_INQUIRY, CRM\_OPPORTUNITY.EMAIL\_INQUIRY,  
CRM\_OPPORTUNITY.PHONE\_INQUIRY, CRM\_OPPORTUNITY.SFDC\_OWNER\_USER\_ID,  
CRM\_OPPORTUNITY.SFDC\_CAMPAIGN\_ID, CRM\_OPPORTUNITY.SFDC\_INQUIRY\_DATE,  
CRM\_OPPORTUNITY.SFDC\_INQUIRY\_DATE\_RPT, CRM\_OPPORTUNITY.ATTRITION\_DATE,  
CRM\_OPPORTUNITY.ORIGINAL\_PROGRAM\_CD, CRM\_OPPORTUNITY.CURRENT\_PROGRAM\_CD,  
CRM\_OPPORTUNITY.ORIGINAL\_SESSION\_CD, CRM\_OPPORTUNITY.CURRENT\_SESSION\_CD,  
CRM\_OPPORTUNITY.ORIGINAL\_LOCATION\_CD, CRM\_OPPORTUNITY.CURRENT\_LOCATION\_CD,  
CRM\_OPPORTUNITY.DUPLICATE\_INQUIRY, CRM\_OPPORTUNITY.GRADUATION\_DATE,  
CRM\_OPPORTUNITY.GRADUATION\_TYPE, CRM\_OPPORTUNITY.PROSPECT\_TYPE,  
CRM\_OPPORTUNITY.DW\_CURRENT\_LOCATION\_ID, CRM\_OPPORTUNITY.ENROLLMENT\_STATUS,  
CRM\_OPPORTUNITY.ENROLLMENT\_SUBSTATUS, CRM\_OPPORTUNITY.DELETED\_RAWINQUIRY,  
CRM\_OPPORTUNITY.DELETED\_OPPORTUNITY, CRM\_OPPORTUNITY.DELETED\_LEAD,  
CRM\_OPPORTUNITY.INQUIRY\_STATUS, CRM\_OPPORTUNITY.LEAD\_CREATE\_DATE,  
CRM\_OPPORTUNITY.LEAD\_CLOSE\_DATE, CRM\_OPPORTUNITY.INQUIRY\_LOCATION,  
CRM\_OPPORTUNITY.KCCL\_IND, CRM\_OPPORTUNITY.SFDC\_ORIGINAL\_USER\_ID,  
CRM\_OPPORTUNITY.SFDC\_CONTACT\_ID, CRM\_OPPORTUNITY.MILITARY\_IND,  
CRM\_OPPORTUNITY.ORIG\_INTENDED\_START\_DATE, CRM\_OPPORTUNITY.SFDC\_ACCOUNT\_ID,  
CRM\_OPPORTUNITY.REPORTING\_DIVISION, CRM\_OPPORTUNITY.INQUIRY\_COUNT,  
CRM\_OPPORTUNITY.APPLICATION\_COUNT, CRM\_OPPORTUNITY.CURRENT\_SESSION\_START\_DATE,  
CRM\_OPPORTUNITY.DELIVERY\_LOCATION\_CD, CRM\_OPPORTUNITY.INQUIRY\_SCORE,  
CRM\_OPPORTUNITY.ATTRITION\_CODE, CRM\_OPPORTUNITY.NEW\_STUDENT\_COUNT,  
CRM\_OPPORTUNITY.SFDC\_MASTER\_LEAD\_ID, CRM\_OPPORTUNITY.CALL\_CENTER\_PARTNER\_NAME,  
CRM\_OPPORTUNITY.CALL\_CENTER\_PROGRAM, CRM\_OPPORTUNITY.CALL\_CENTER\_DATE,  
CRM\_OPPORTUNITY.ELOQUA\_TIMESTAMP, CRM\_OPPORTUNITY.EQUIFAX\_TIMESTAMP,  
CRM\_OPPORTUNITY.ORIGINAL\_BRAND, CRM\_OPPORTUNITY.ORIGINAL\_GRAD\_LEVEL,  
CRM\_OPPORTUNITY.ORIGINAL\_CHANNEL ,  
CRM\_OPPORTUNITY.INTERNATIONAL\_IND, CRM\_OPPORTUNITY.NON\_MATRIC\_INQUIRY,  
CRM\_OPPORTUNITY.APPLICATION\_REGION, CRM\_OPPORTUNITY.EQUIFAX\_TRANSITION\_ID,  
CRM\_OPPORTUNITY.RPT\_DIV\_GRAD\_LEVEL, CRM\_OPPORTUNITY.SFDC\_ORIGINAL\_LEAD\_ID,  
CRM\_OPPORTUNITY.ROUTING\_LOCATION\_CD, CRM\_OPPORTUNITY.CRM\_ROUTING\_LOCATION\_ID,  
CRM\_OPPORTUNITY.ORIGINAL\_LEAD\_CREATE\_DATE,  
CRM\_OPPORTUNITY.EP\_EVENT\_ID, CRM\_OPPORTUNITY.CRM\_EP\_EVENT\_LEGACY\_ID,  
CRM\_OPPORTUNITY.CRM\_EP\_OWNER\_LEGACY\_ID,  
CRM\_OPPORTUNITY.CRM\_EP\_ACCOUNT\_LEGACY\_ID,  
nvl(CRM\_PROSPECT.CRM\_PROSPECT\_ID, -1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_EVENT\_LEGACY\_ID, -1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_ACCOUNT\_LEGACY\_ID,-1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_OWNER\_LEGACY\_ID,-1)  
FROM  
SFDC\_RAWINQUIRY\_STG, CRM\_OPPORTUNITY, CRM\_PROSPECT, CRM\_EVENT\_LEGACY  
WHERE  
SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_ID=CRM\_OPPORTUNITY.SFDC\_RAW\_INQUIRY\_ID(+)  
AND  
SFDC\_RAWINQUIRY\_STG.LEAD\_ID=CRM\_PROSPECT.SFDC\_LEAD\_ID(+)  
AND  
SFDC\_RAWINQUIRY\_STG.CONTACT\_ID IS NULL  
AND  
CRM\_Event\_Legacy.Legacy\_SFDC\_Opportunity\_ID(+) LIKE  
SFDC\_RawInquiry\_Stg.EP\_Event\_ID || ‘%’  
AND  
CRM\_Event\_Legacy.DW\_Closed\_Date(+) = to\_date(‘01012500’,‘mmddyyyy’)  
UNION  
SELECT /_+ use\_hash(SFDC\_RAWINQUIRY\_STG) use\_hash(CRM\_OPPORTUNITY)  
use\_hash(CRM\_PROSPECT) use\_hash(CRM\_EVENT\_LEGACY) \*/  
SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_ID, SFDC\_RAWINQUIRY\_STG.LEAD\_ID,  
SFDC\_RAWINQUIRY\_STG.BRAND, SFDC\_RAWINQUIRY\_STG.GRAD\_LEVEL,  
SFDC\_RAWINQUIRY\_STG.CHANNEL, SFDC\_RAWINQUIRY\_STG.VENDOR\_CODE,  
SFDC\_RAWINQUIRY\_STG.VENDOR\_TIMESTAMP,  
SFDC\_RAWINQUIRY\_STG.VENDOR\_TRACKING\_NUMBER, SFDC\_RAWINQUIRY\_STG.ADDRESS\_STREET,  
SFDC\_RAWINQUIRY\_STG.ADDRESS\_CITY, SFDC\_RAWINQUIRY\_STG.ADDRESS\_STATE,  
SFDC\_RAWINQUIRY\_STG.ADDRESS\_POSTAL\_CODE, SFDC\_RAWINQUIRY\_STG.ADDRESS\_COUNTRY,  
SFDC\_RAWINQUIRY\_STG.EMAIL, SFDC\_RAWINQUIRY\_STG.PHONE,  
SFDC\_RAWINQUIRY\_STG.EP\_PRESENTER\_DSI, SFDC\_RAWINQUIRY\_STG.REP\_DSI,  
SFDC\_RAWINQUIRY\_STG.LEGACY\_SFID, SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_DELETED,  
SFDC\_RAWINQUIRY\_STG.SFDC\_INQUIRY\_CREATE\_DATE, SFDC\_RAWINQUIRY\_STG.OWNER\_ID,  
SFDC\_RAWINQUIRY\_STG.SFDC\_INQUIRY\_UPDATE\_DATE,  
SFDC\_RAWINQUIRY\_STG.PROGRAM\_OF\_INTEREST,  
SFDC\_RAWINQUIRY\_STG.LOCATION\_PREFERENCE, SFDC\_RAWINQUIRY\_STG.INQUIRY\_STATUS,  
SFDC\_RAWINQUIRY\_STG.PROSPECT\_TYPE, SFDC\_RAWINQUIRY\_STG.EP\_SCORE\_EXPLICIT,  
SFDC\_RAWINQUIRY\_STG.EP\_SCORE\_IMPLICIT, SFDC\_RAWINQUIRY\_STG.CAMPAIGN\_ID,  
SFDC\_RAWINQUIRY\_STG.DUPLICATE, SFDC\_RAWINQUIRY\_STG.GRADUATION\_DATE,  
SFDC\_RAWINQUIRY\_STG.GRADUATION\_TYPE, SFDC\_RAWINQUIRY\_STG.ORIGINAL\_LOCATION\_CD,  
SFDC\_RAWINQUIRY\_STG.INQUIRY\_SCORE, SFDC\_RAWINQUIRY\_STG.ELOQUA\_TIMESTAMP,  
SFDC\_RAWINQUIRY\_STG.EQUIFAX\_TIMESTAMP, SFDC\_RAWINQUIRY\_STG.ASSIGN\_KCCL,  
SFDC\_RAWINQUIRY\_STG.ASSIGN\_MILITARY, SFDC\_RAWINQUIRY\_STG.LEAD\_STATUS,  
SFDC\_RAWINQUIRY\_STG.LEAD\_CREATE\_DATE, SFDC\_RAWINQUIRY\_STG.LEAD\_DELETED,  
SFDC\_RAWINQUIRY\_STG.LEAD\_MASTER\_RECORD\_ID, SFDC\_RAWINQUIRY\_STG.LEGACY\_LEAD\_ID,  
SFDC\_RAWINQUIRY\_STG.ASSIGN\_INTERNATIONAL,  
SFDC\_RAWINQUIRY\_STG.CONTACT\_ID, SFDC\_RAWINQUIRY\_STG.EQUIFAX\_TRANSITION\_ID,  
SFDC\_RAWINQUIRY\_STG.EP\_EVENT\_ID,  
CRM\_OPPORTUNITY.CRM\_OPPORTUNITY\_ID, CRM\_OPPORTUNITY.CRM\_PROSPECT\_ID,  
CRM\_OPPORTUNITY.CRM\_CAMPAIGN\_ID, CRM\_OPPORTUNITY.CRM\_OWNER\_USER\_ID,  
CRM\_OPPORTUNITY.CRM\_ORIGINAL\_USER\_ID, CRM\_OPPORTUNITY.STAGE,  
CRM\_OPPORTUNITY.BRAND, CRM\_OPPORTUNITY.GRAD\_LEVEL, CRM\_OPPORTUNITY.CHANNEL,  
CRM\_OPPORTUNITY.VENDOR\_CODE, CRM\_OPPORTUNITY.VENDOR\_TIMESTAMP,  
CRM\_OPPORTUNITY.VENDOR\_TRACKING\_NUMBER, CRM\_OPPORTUNITY.LOCATION\_PREFERENCE,  
CRM\_OPPORTUNITY.EP\_SCORE\_EXPLICIT, CRM\_OPPORTUNITY.EP\_SCORE\_IMPLICIT,  
CRM\_OPPORTUNITY.LEAD\_STATUS, CRM\_OPPORTUNITY.ADVISOR\_DSI,  
CRM\_OPPORTUNITY.EP\_PRESENTER\_DSI, CRM\_OPPORTUNITY.REP\_DSI,  
CRM\_OPPORTUNITY.LEGACY\_SFID, CRM\_OPPORTUNITY.CLOSED\_REASON,  
CRM\_OPPORTUNITY.CLOSED\_DETAIL, CRM\_OPPORTUNITY.APPLICATION\_DATE,  
CRM\_OPPORTUNITY.REGISTRATION\_DATE, CRM\_OPPORTUNITY.ATTENDED\_DATE,  
CRM\_OPPORTUNITY.SFDC\_RAW\_INQUIRY\_ID, CRM\_OPPORTUNITY.SFDC\_LEAD\_ID,  
CRM\_OPPORTUNITY.SFDC\_OPPORTUNITY\_ID, CRM\_OPPORTUNITY.DW\_ORGINAL\_LOCATION\_ID,  
CRM\_OPPORTUNITY.ADDRESS\_STREET\_INQUIRY, CRM\_OPPORTUNITY.ADDRESS\_CITY\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_STATE\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_POSTAL\_CODE\_INQUIRY,  
CRM\_OPPORTUNITY.ADDRESS\_COUNTRY\_INQUIRY, CRM\_OPPORTUNITY.EMAIL\_INQUIRY,  
CRM\_OPPORTUNITY.PHONE\_INQUIRY, CRM\_OPPORTUNITY.SFDC\_OWNER\_USER\_ID,  
CRM\_OPPORTUNITY.SFDC\_CAMPAIGN\_ID, CRM\_OPPORTUNITY.SFDC\_INQUIRY\_DATE,  
CRM\_OPPORTUNITY.SFDC\_INQUIRY\_DATE\_RPT, CRM\_OPPORTUNITY.ATTRITION\_DATE,  
CRM\_OPPORTUNITY.ORIGINAL\_PROGRAM\_CD, CRM\_OPPORTUNITY.CURRENT\_PROGRAM\_CD,  
CRM\_OPPORTUNITY.ORIGINAL\_SESSION\_CD, CRM\_OPPORTUNITY.CURRENT\_SESSION\_CD,  
CRM\_OPPORTUNITY.ORIGINAL\_LOCATION\_CD, CRM\_OPPORTUNITY.CURRENT\_LOCATION\_CD,  
CRM\_OPPORTUNITY.DUPLICATE\_INQUIRY, CRM\_OPPORTUNITY.GRADUATION\_DATE,  
CRM\_OPPORTUNITY.GRADUATION\_TYPE, CRM\_OPPORTUNITY.PROSPECT\_TYPE,  
CRM\_OPPORTUNITY.DW\_CURRENT\_LOCATION\_ID, CRM\_OPPORTUNITY.ENROLLMENT\_STATUS,  
CRM\_OPPORTUNITY.ENROLLMENT\_SUBSTATUS, CRM\_OPPORTUNITY.DELETED\_RAWINQUIRY,  
CRM\_OPPORTUNITY.DELETED\_OPPORTUNITY, CRM\_OPPORTUNITY.DELETED\_LEAD,  
CRM\_OPPORTUNITY.INQUIRY\_STATUS, CRM\_OPPORTUNITY.LEAD\_CREATE\_DATE,  
CRM\_OPPORTUNITY.LEAD\_CLOSE\_DATE, CRM\_OPPORTUNITY.INQUIRY\_LOCATION,  
CRM\_OPPORTUNITY.KCCL\_IND, CRM\_OPPORTUNITY.SFDC\_ORIGINAL\_USER\_ID,  
CRM\_OPPORTUNITY.SFDC\_CONTACT\_ID, CRM\_OPPORTUNITY.MILITARY\_IND,  
CRM\_OPPORTUNITY.ORIG\_INTENDED\_START\_DATE, CRM\_OPPORTUNITY.SFDC\_ACCOUNT\_ID,  
CRM\_OPPORTUNITY.REPORTING\_DIVISION, CRM\_OPPORTUNITY.INQUIRY\_COUNT,  
CRM\_OPPORTUNITY.APPLICATION\_COUNT, CRM\_OPPORTUNITY.CURRENT\_SESSION\_START\_DATE,  
CRM\_OPPORTUNITY.DELIVERY\_LOCATION\_CD, CRM\_OPPORTUNITY.INQUIRY\_SCORE,  
CRM\_OPPORTUNITY.ATTRITION\_CODE, CRM\_OPPORTUNITY.NEW\_STUDENT\_COUNT,  
CRM\_OPPORTUNITY.SFDC\_MASTER\_LEAD\_ID, CRM\_OPPORTUNITY.CALL\_CENTER\_PARTNER\_NAME,  
CRM\_OPPORTUNITY.CALL\_CENTER\_PROGRAM, CRM\_OPPORTUNITY.CALL\_CENTER\_DATE,  
CRM\_OPPORTUNITY.ELOQUA\_TIMESTAMP, CRM\_OPPORTUNITY.EQUIFAX\_TIMESTAMP,  
CRM\_OPPORTUNITY.ORIGINAL\_BRAND, CRM\_OPPORTUNITY.ORIGINAL\_GRAD\_LEVEL,  
CRM\_OPPORTUNITY.ORIGINAL\_CHANNEL ,  
CRM\_OPPORTUNITY.INTERNATIONAL\_IND, CRM\_OPPORTUNITY.NON\_MATRIC\_INQUIRY,  
CRM\_OPPORTUNITY.APPLICATION\_REGION, CRM\_OPPORTUNITY.EQUIFAX\_TRANSITION\_ID,  
CRM\_OPPORTUNITY.RPT\_DIV\_GRAD\_LEVEL, CRM\_OPPORTUNITY.SFDC\_ORIGINAL\_LEAD\_ID,  
CRM\_OPPORTUNITY.ROUTING\_LOCATION\_CD, CRM\_OPPORTUNITY.CRM\_ROUTING\_LOCATION\_ID,  
CRM\_OPPORTUNITY.ORIGINAL\_LEAD\_CREATE\_DATE,  
CRM\_OPPORTUNITY.EP\_EVENT\_ID, CRM\_OPPORTUNITY.CRM\_EP\_EVENT\_LEGACY\_ID,  
CRM\_OPPORTUNITY.CRM\_EP\_OWNER\_LEGACY\_ID,  
CRM\_OPPORTUNITY.CRM\_EP\_ACCOUNT\_LEGACY\_ID,  
nvl(CRM\_PROSPECT.CRM\_PROSPECT\_ID,-1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_EVENT\_LEGACY\_ID, -1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_ACCOUNT\_LEGACY\_ID,-1),  
nvl(CRM\_EVENT\_LEGACY.CRM\_OWNER\_LEGACY\_ID,-1)  
FROM  
SFDC\_RAWINQUIRY\_STG, CRM\_OPPORTUNITY, CRM\_PROSPECT, CRM\_EVENT\_LEGACY  
WHERE  
SFDC\_RAWINQUIRY\_STG.RAW\_INQUIRY\_ID=CRM\_OPPORTUNITY.SFDC\_RAW\_INQUIRY\_ID(+)  
AND  
SFDC\_RAWINQUIRY\_STG.CONTACT\_ID=CRM\_PROSPECT.SFDC\_CONTACT\_ID(+)  
AND  
SFDC\_RAWINQUIRY\_STG.CONTACT\_ID IS NOT NULL  
AND  
CRM\_Event\_Legacy.Legacy\_SFDC\_Opportunity\_ID(+) LIKE  
SFDC\_RawInquiry\_Stg.EP\_Event\_ID || ‘%’  
AND  
CRM\_Event\_Legacy.DW\_Closed\_Date(+) = to\_date(‘01012500’,‘mmddyyyy’)

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 8, 2010, 1:19pm UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/2 "2010-09-08T13:19:00Z")

</div>

Any feedback will be appreciated.  
Thank you

---

<div class="post-metadata">

**Author:** ![Anonymous1](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@Anonymous1](https://forums.toadworld.com/u/Anonymous1)\
**Post date:** [September 8, 2010, 2:04pm UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/3 "2010-09-08T14:04:27Z")

</div>

This has usually been a problem for me if I am creating a very large result set  
that possibly needs to be sorted.

I noticed you had a UNION

A UNION is actually a UNION ALL with an invisible outer SELECT that does a  
DISTINCT – that is it requires sorting.

Here are some things to try (assuming non of the objects in the FROM clauses are  
views)

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 8, 2010, 2:55pm UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/4 "2010-09-08T14:55:31Z")

</div>

Anybody any clues? Would an index on SFDC\_RawInquiry\_Stg.EP\_Event\_ID work? or  
any other hint?  
thank you

---

<div class="post-metadata">

**Author:** ![Anonymous1](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@Anonymous1](https://forums.toadworld.com/u/Anonymous1)\
**Post date:** [September 9, 2010, 8:24am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/5 "2010-09-09T08:24:15Z")

</div>

Did you try the suggestion I sent? I think it is a sorting issue and no amount  
of indexes or hints will help.

---

<div class="post-metadata">

**Author:** ![Anonymous1](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@Anonymous1](https://forums.toadworld.com/u/Anonymous1)\
**Post date:** [September 9, 2010, 8:28am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/6 "2010-09-09T08:28:05Z")

</div>

Reduce the columns to one column. Keep the tables and joins.

Then run it once with the old join and once with the new and check the rows returned. See if more rows are returned. You may have hit a max on temp space. Also do row counts on the two separate parts.

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 9, 2010, 8:55am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/7 "2010-09-09T08:55:19Z")

</div>

Well, we know that all the overhead associated with the LIKE is the culprit. If  
one or both of the IDs in the offending join is numeric, try to\_charring them. I  
don’t know if the outer join will fail with the addition of an index, but  
I’m thinking to save the additional overhead for an on-the-fly type  
conversion. Grasping at straws here.

This paragraph is food for thought, but maybe you can fool around with creating  
in-line views to obtain subsets of data that can be matched in a way that  
eliminates the LIKE clause in your outer join.

Again, just ideas that might be immediate throw-aways, but perhaps it’ll  
spark something.

Best regards,  
Mike

Michael S. Zarzycki, MTS | Manager of Engineering IT | Sensata Technologies,  
Inc. | voice: 508-236-1015 | fax: 508-236-3701 | [www.sensata.com](http://www.sensata.com) | The World  
Depends on Sensors and Controls

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 9, 2010, 9:47am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/8 "2010-09-09T09:47:47Z")

</div>

Thank you for your response.  
Answers below…

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [September 10, 2010, 7:31am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/9 "2010-09-10T07:31:58Z")

</div>

Hi Henry,

> > 1. If the UNION ALL works then go see a DBA Already  
> > talked to DBA. Will not do anything. They are saying  
> > tablespace is large enough. They believe the SQL needs fine tuning.

1. Do you know how big the TEMP tablespace currently is by any chance?

2. What does Toad tell you the explain plan will be for the statement  
with the UNION? In the editor, press CTRL+E and the output area will  
give you many details.

Look through the details for the various sizes - especially those being  
input to the sort phase(s). I suspect that one or more of these is  
bigger than the current size of the TEMP tablespace.

If your DBA persists in telling you that the tablespace is big enough  
already, show them the explain pan details and say that that is how much  
is required.

If the DBA then persists in saying that you still need to optimise the  
query, tell him/her to do it for you because you are unable to optimise  
it any further.

After a while, I suspect you may well find yourself with a (much) bigger  
TEMP tablespace! 😉

Cheers,  
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.  
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk)

---

<div class="post-metadata">

**Author:** ![Anonymous1](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@Anonymous1](https://forums.toadworld.com/u/Anonymous1)\
**Post date:** [September 10, 2010, 7:50am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/10 "2010-09-10T07:50:58Z")

</div>

Two last things to try.

See if you can get the DBAs to temporarily give you rights to set the  
sort\_area\_size to the max and then you may need as much temp space.

The other thing to try is materialize each query. Hat is create a table of each  
half. The UNION them together in a query. If that croaks on temp space then you  
have proof you need more space because a strait union of two tables can’t  
be optimized any further.

And one more thing. I presume the query you are running is needed. Someone needs  
the results. You are not doing this because you have nothing better to do. In  
that case tell your boss to fight the DBA. Someone has to have more clout.

He should be prepared to change it temporarily to see if that is the problem.

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [September 10, 2010, 9:38am UTC](https://forums.toadworld.com/t/when-using-where-clause-like-error-is-given-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/9801/11 "2010-09-10T09:38:15Z")

</div>

Thank you all for your responses.

I went through each query separated by the UNION, compared against each other  
and they are distinct already. Hence, no UNION is needed but UNION ALL and the  
result set comes really fast. However, our Data Architect needs to work on this  
join proposed, but that is another story.