database - java.sql.SQLException：ORA-01652：无法在表空间 TEMP 中将临时段扩展 128（即使在扩展之后）
问题描述
我们的数据加载在运行了几个小时后失败了。DBA温度升高tablespace三次。它仍然失败。错误的原因可能是什么。块大小或其他什么重要吗？
尝试CRM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
使用作业 125,840 的数据集 crm-expenditus 提取此加载规则的数据时出错
**java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in
tablespace TEMP**
我已经按照某些人的建议放置了 sql。
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" )
)
如果它在某种程度上有帮助，下面是跟踪输出。
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 的方法，以在可能的情况下删除连接和排序。否则，没有任何解决方法。
如果您可以获得所涉及 SQL 的解释计划，它应该至少为您提供优化器期望需要多少 TEMP 空间的大致估计。
推荐阅读
- java - 正则表达式提取方程中的数学变量
- javascript - 在 html 中制作可拖动的形状
- r - 如何将时间（HH：MM；SS）转换为秒并在excel的另一列中写入秒？
- python-3.x - 如何在线程内启动异步目标
- dataframe - 按行名合并多个数据框中的选定列
- excel - 在 AWS Quicksight 中从 Excel 重新创建帕累托图
- rust - 在可变借用编译无错误后不可变借用
- reactjs - 使用 scrollTo 反应 ref 会触发滚动事件，即使我们在之后添加它并删除它
- reactjs - 在反应中单击时，从多个 div 列表中切换仅一个 div 的下拉列表
- linux - 查找以“go”开头的目录，但不查找名称包含“work”或“play”的目录