Reference:
TechOnTheNet.com ORA-01652: unable to extend temp segment by num in tablespace name
After the topic「PLSQL case when sum count ORA-00934: group function is not allowed here」, the ORA-01652 error happened,it let me know that use CASE statement inside the aggregate function would create temp segment and might cause that problem.
Situation:
select sum(case when ... then 1 else 0 end) "cnt1", sum(case when ... then 1 else 0 end) "cnt2" from some_table
Solution: Don’t use that while the selected tuples are not small, e.g.
select a.key1, a.key2, a.cnt1, b.cnt2 from (select key1, key2, count(*) as cnt1 from some_table where ... -- condition for counting cnt1 group by key1, key2) a left outer join (select key1, key2, count(*) as cnt2 from some_table where ... -- condition for counting cnt2 group by key1, key2) b on b.key1 = a.key1 and b.key2 = a.key2