database - java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP(Even after extending)

Question

Our Dataload got failed after running many hours. DBA increased temp tablespace thrice. Still it is failing. What could be the reason of error. Does block size or anything else matters?

There is an error while trying to ingest data for this load rule CRM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO with dataset crm-expenditures for job 125,840

**java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in
tablespace TEMP**

I have put the sql as some people suggested me to do.

SELECT
    *
FROM
    (
        SELECT  /*+ leading(ppp) index(pp PA_PROJECTS_U1) */    'PROJ_EXP-'
            || pp.project_id
            || '-'
            || nvl(to_char(pt.task_id), 'NO_TASK')
            || '-'
            || to_char(pei.expenditure_item_id)                            AS ecc_spec_id,
            greatest(pp.last_update_date, pt.last_update_date, pei.last_update_date, pet.last_update_date, pe.last_update_date,
                     nvl(pap.last_update_date, pp.last_update_date),
                     nvl(pj.last_update_date, pp.last_update_date),
                     nvl(ap1.last_update_date, pp.last_update_date))        AS ecc_last_update_date,
            pp.project_id                                                      AS project_id,
            pp.segment1                                                        AS project_number,
            pp.org_id                                                          AS org_id,
            pt.task_id                                                         AS task_id,
            pt.task_number                                                     AS task_number,
            pei.expenditure_item_id                                            AS item_id,
            pei.expenditure_type                                               AS exp_type,
            pet.expenditure_category                                           AS exp_category,
            pet.revenue_category_code                                          AS exp_revenue_category_code,
            hou1.name                                                          AS exp_org,
            pei.expenditure_item_date                                          AS exp_item_date,
            pei.quantity                                                       AS exp_quantity,
            flv.meaning                                                        AS uom,
            pei.projfunc_currency_code                                         AS exp_projfunc_currency_code,
            nvl(pei.burden_cost, 0)                                      AS exp_burdened_cost,
            pei.accrued_revenue                                                AS exp_accrued_revenue,
            pei.bill_amount                                                    AS exp_bill_amount,
            pei.project_currency_code                                          AS exp_project_currency_code,
            nvl(pei.project_burdened_cost, 0)                            AS exp_project_burdened_cost,
            pei.project_raw_revenue                                            AS exp_project_raw_revenue,
            pei.project_bill_amount                                            AS exp_project_bill_amount,
            pei.bill_trans_currency_code                                       AS exp_bill_trans_currency_code,
            pei.bill_trans_bill_amount                                         AS exp_bill_trans_bill_amount,
            pei.non_labor_resource                                             AS exp_non_labor_resc,
            hou2.name                                                          AS exp_non_labor_org,
            pe.incurred_by_person_id                                           AS exp_incurred_by_person_id,
            pap.full_name                                                      AS exp_employee_name,
            pap.employee_number                                                AS exp_employee_number,
            pei.job_id                                                         AS exp_person_job_id,
            pj.name                                                            AS exp_person_job_name,
            pe.vendor_id                                                       AS supplier_id,
            ap1.vendor_name                                                    AS supplier_name,
            hou1.language                                                      AS language
        FROM
            pa_projects_all               pp,
            pa_tasks                      pt,
            pa_expenditure_items_all      pei,
            pa_expenditure_types          pet,
            pa_expenditures_all           pe,
            per_all_people_f              pap,
            per_jobs                      pj,
            ap_suppliers                  ap1,
            hr_all_organization_units_tl  hou1,
            hr_all_organization_units_tl  hou2,
            fnd_lookup_values             flv,
            (
                SELECT DISTINCT
                    project_id
                FROM
                    pjm_project_parameters
            )                             ppp
        WHERE
                pp.start_date >= nvl(TO_DATE('01-JAN-18', 'RRRR/MM/DD HH24:MI:SS'), pp.start_date)
            AND pp.project_type <> 'AWARD_PROJECT'
            AND pp.template_flag = 'N'
            AND pp.project_id = ppp.project_id
            AND pt.project_id = pp.project_id
            AND pei.project_id = pt.project_id
            AND pei.task_id = pt.task_id
            AND pet.expenditure_type = pei.expenditure_type
            AND pe.expenditure_id = pei.expenditure_id
            AND pe.org_id = pei.org_id
            AND pap.person_id (+) = pe.incurred_by_person_id
            AND trunc(sysdate) BETWEEN nvl(pap.effective_start_date, sysdate - 1) AND nvl(pap.effective_end_date, sysdate + 1)
            AND pj.job_id (+) = pei.job_id
            AND ap1.vendor_id (+) = pe.vendor_id
            AND hou1.organization_id = nvl(pei.override_to_organization_id, pe.incurred_by_organization_id)
            AND hou2.organization_id (+) = pei.organization_id
            AND ( hou2.language IS NULL
                  OR hou2.language = hou1.language )
            AND flv.view_application_id = 275
            AND flv.security_group_id = 0
            AND flv.lookup_type = 'UNIT'
            AND flv.lookup_code = pet.unit_of_measure
            AND flv.language = hou1.language
            AND hou1.language IN ( 'US' )
    ) PIVOT (
        MAX ( exp_org )
    AS exp_org, MAX ( uom ) AS uom, MAX ( exp_non_labor_org ) AS exp_non_labor_org
        FOR language
        IN ( 'US' "US" )
    )

Below is trace output if it helps in some way.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.87       2.87          0          0          6           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1   3640.57    6131.92    9944538    9990033        260           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   3643.44    6134.79    9944538    9990033        266           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH GROUP BY PIVOT (cr=0 pr=0 pw=0 time=211 us starts=1 cost=2368710 size=159986583 card=301293)
         0          0          0   HASH JOIN RIGHT OUTER (cr=0 pr=0 pw=0 time=24 us starts=1 cost=2340094 size=159986583 card=301293)
     20334      20334      20334    TABLE ACCESS FULL AP_SUPPLIERS (cr=1027 pr=0 pw=0 time=8264 us starts=1 cost=168 size=752358 card=20334)
         0          0          0    HASH JOIN RIGHT OUTER (cr=0 pr=0 pw=0 time=26 us starts=1 cost=2339923 size=148838742 card=301293)
      7569       7569       7569     TABLE ACCESS FULL PER_JOBS (cr=183 pr=181 pw=0 time=53049 us starts=1 cost=31 size=264915 card=7569)
         0          0          0     FILTER  (cr=0 pr=0 pw=0 time=25 us starts=1)
         0          0          0      NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=23 us starts=1 cost=2339890 size=138293487 card=301293)
         0          0          0       HASH JOIN  (cr=0 pr=0 pw=0 time=19 us starts=1 cost=2339889 size=131062455 card=301293)
      3488       3488       3488        TABLE ACCESS FULL HR_ALL_ORGANIZATION_UNITS_TL (cr=30 pr=0 pw=0 time=1511 us starts=1 cost=6 size=83688 card=3487)
         0          0          0        FILTER  (cr=0 pr=0 pw=0 time=20 us starts=1)
         0          0          0         HASH JOIN RIGHT OUTER (cr=0 pr=0 pw=0 time=20 us starts=1 cost=2339880 size=123831423 card=301293)
    482564     482564     482564          TABLE ACCESS FULL PER_ALL_PEOPLE_F (cr=24850 pr=0 pw=0 time=496147 us starts=1 cost=3887 size=26058456 card=482564)
         0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=83 us starts=1 cost=2335031 size=31579863 card=88459)
 964899956  964899956  964899956           HASH JOIN  (cr=9963945 pr=9944251 pw=0 time=1257291836 us starts=1 cost=2272141 size=57476164 card=173644)
       440        440        440            TABLE ACCESS BY INDEX ROWID BATCHED FND_LOOKUP_VALUES (cr=345 pr=11 pw=0 time=8731 us starts=1 cost=4 size=60 card=1)
       440        440        440             INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=8 pr=5 pw=0 time=4739 us starts=1 cost=3 size=0 card=1)(object id 10051183)
 964899956  964899956  964899956            HASH JOIN  (cr=9963600 pr=9944240 pw=0 time=1017124322 us starts=1 cost=2272127 size=307417251 card=1134381)
      1095       1095       1095             TABLE ACCESS FULL PA_EXPENDITURE_TYPES (cr=30 pr=28 pw=0 time=10207 us starts=1 cost=6 size=58035 card=1095)
 964899956  964899956  964899956             HASH JOIN  (cr=9963569 pr=9944212 pw=0 time=846695062 us starts=1 cost=2272112 size=247295058 card=1134381)
   1358758    1358758    1358758              HASH JOIN  (cr=19092 pr=0 pw=0 time=469629 us starts=1 cost=7758 size=113438100 card=1134381)
     19048      19048      19048               HASH JOIN  (cr=4302 pr=0 pw=0 time=55357 us starts=1 cost=5230 size=1806098 card=25438)
     31692      31692      31692                TABLE ACCESS FULL PJM_PROJECT_PARAMETERS (cr=499 pr=0 pw=0 time=5159 us starts=1 cost=78 size=158460 card=31692)
      5436       5436       5436                TABLE ACCESS BY INDEX ROWID BATCHED PA_PROJECTS_ALL (cr=3803 pr=0 pw=0 time=33748 us starts=1 cost=5152 size=147972 card=2242)
      7889       7889       7889                 INDEX FULL SCAN PA_PROJECTS_U1 (cr=43 pr=0 pw=0 time=2952 us starts=1 cost=43 size=0 card=7889)(object id 10074361)
    351540     351540     351540               TABLE ACCESS FULL PA_TASKS (cr=14789 pr=0 pw=0 time=139478 us starts=1 cost=2272 size=10194660 card=351540)
 130267813  130267813  130267813              TABLE ACCESS FULL PA_EXPENDITURE_ITEMS_ALL (cr=9944477 pr=9944212 pw=0 time=213772932 us starts=1 cost=1945518 size=19299868662 card=163558209)
         0          0          0           TABLE ACCESS FULL PA_EXPENDITURES_ALL (cr=0 pr=0 pw=0 time=0 us starts=0 cost=50307 size=526480734 card=20249259)
         0          0          0       TABLE ACCESS BY INDEX ROWID BATCHED HR_ALL_ORGANIZATION_UNITS_TL (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=24 card=1)
         0          0          0        INDEX RANGE

Answer 1

The SQL involved is constructing an intermediate result set - most likely because of a join or a sort. There isn't enough space in your TEMP tablespace to hold it. You will either need to add one or more data files to the tablespace (assuming you have enough space on your physical storage to do so), or work with a smaller data set. Alternatively, you could look for ways to modify your SQL to remove joins and sorts where possible. Otherwise, there isn't any workaround.

If you can get an explain plan of the SQL involved it should give you at least a ballpark estimate of how much TEMP space the optimizer is expecting to need.