oracle 12c – ORA-01652 – How to reduce TEMP segment usage?

In production we have a failing batch with an error:

ORA-01652: unable to extend temp segment by 128 in tablespace MYTABLESPACE

The TEMP segment has already been increased to its maximum tolerable according to the DBA.
Indeed, the SQL request below performs so many outer joins that this behaviour is obvious…

There are two dates as inputs : from and to.

SELECT *
    /* In reality, not all columns are included but "only" the 122 required */
                    
FROM TABLE1 t1
    INNER JOIN TABLE2 t2
        ON t2.ID = t1.ID_T2
    INNER JOIN TABLE3 t3
        ON t3.ID = t1.ID_T3
    INNER JOIN TABLE4 t4
        ON t4.ID_T1 = t1.ID
    LEFT JOIN TABLE5 t5
        ON t5.ID_T1 = t1.ID
    LEFT JOIN TABLE6 t6
        ON t6.ID_T1 = t1.ID
    LEFT JOIN TABLE7 t7
        ON t7.ID_T3 = t3.ID AND t7.DATE BETWEEN TO_DATE('xxxx-xx-xx', 'YYYY-MM-DD') AND TO_DATE('yyyy-yy-yy', 'YYYY-MM-DD')
    LEFT JOIN TABLE8 t8
        ON t8.ID_T1 = t1.ID
    LEFT JOIN TABLE9 t9a
        ON t9a.ID = t4.ID_T9
    LEFT JOIN TABLE10 t10
        ON t10.ID_T4 = t4.ID
    LEFT JOIN TABLE11 t11
        ON t11.ID_T4 = t4.ID AND t11.DATE BETWEEN TO_DATE('xxxx-xx-xx', 'YYYY-MM-DD') AND TO_DATE('yyyy-yy-yy', 'YYYY-MM-DD')
    LEFT JOIN TABLE9 t9b
        ON t9b.ID = t11.ID_T9
    LEFT JOIN TABLE12 t12
        ON t12.ID_T1 = t1.ID
    LEFT JOIN TABLE13 t13
        ON t13.ID_T4 = t4.ID
    LEFT JOIN TABLE14 t14
        ON t14.ID_T13 = t13.ID
    LEFT JOIN TABLE15 t15
        ON t15.ID_T4 = t4.ID
    LEFT JOIN TABLE16 t16
        ON t16.ID_T4 = t4.ID
    LEFT JOIN TABLE17 t17
        ON t17.ID_T1 = t1.ID
    LEFT JOIN TABLE18 t18
        ON t18.ID_T17 = t17.ID
    LEFT JOIN TABLE19 t19
        ON t19.ID_T17 = t17.ID
    LEFT JOIN TABLE20 t20
        ON t20.ID_T17 = t17.ID
WHERE 
    t4.something IS NOT NULL OR t11.something IS NOT NULL
ORDER BY t2.ID, t1.ID, t3.ID, t4.ID, t14.ID, t18.ID, t5.DATE DESC;

I’m very pessimist about this issue which raises a very big conception problem. We should have split it in several steps.

Do you see a way to reduce the TEMP segment usage without a change of the code that needs the returned data as-is? For example:

  • Can a subquery performing the WHERE clause, help?
  • Can a smaller interval between the date from and to, help?

In general, what affects the TEMP segment usage?