java.sql.SQLException: ORA-01652: 无法在表空间 TEMP 中将临时段扩展 128(即使在扩展之后)

2021-12-30 00:00:00 database oracle11g oracle query-optimization database-administration

我们的数据加载在运行数小时后失败.DBA 将临时 tablespace 增加了三次.它仍然失败.什么可能是错误的原因.块大小或其他任何事情重要吗?

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?

尝试时出错为这个加载规则摄取数据CRM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO使用数据集 crm-expenditus for job 125,840

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**

我已经按照一些人的建议将 sql 放入了.

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 SCAN HR_ALL_ORGANIZATION_UNTS_TL_PK (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 10049431) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 db file sequential read 125 0.00 0.09 db file scattered read 22 0.00 0.05 db file parallel read 1 0.00 0.00 PGA memory operation 121 0.00 0.01 direct path read 2737 0.02 9.60 direct path write temp 1230152 2.10 2532.69 Disk file operations I/O 45 0.00 0.01 latch free 10 0.00 0.00 log file sync: SCN ordering 1 0.00 0.00 latch: enqueue hash chains 1 0.00 0.00 SQL*Net break/reset to client 2 0.00 0.00 SQL*Net message from client 1 0.20 0.20 ******************************************************************************** SQL ID: 2bpdh1w6xph3h Plan Hash: 3884771224

推荐答案

所涉及的 SQL 正在构建一个中间结果集 - 很可能是因为连接或排序.您的 TEMP 表空间中没有足够的空间来容纳它.您将需要向表空间添加一个或多个数据文件(假设您的物理存储空间有足够的空间来这样做)，或者使用较小的数据集.或者，您可以寻找修改 SQL 以在可能的情况下删除连接和排序的方法.否则，没有任何解决方法.

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.

如果您可以获得所涉及的 SQL 的解释计划，它应该至少为您提供优化器期望需要多少 TEMP 空间的大致估计.

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.

相关文章