ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段
1,同事说执行sql报错
同事在plsql里面执行sql报错,报错信息:ora-01652 无法通过128 (在表空间 TEMP中)扩展temp段,如下图所示:
2,查看报错sql语句
Sql比较长,而且无法扩展temp字段,那么基本推断可能有如下2种情况:
(1)oracle的temp临时表空间太小了;
(2)一个性能非常差的笛卡尔积的带全表扫描的sql占用的资源超过了temp的表空间大小。
先看执行的sql语句,sql比较长,所以这种属于(1)(2)的结合情况了,sql如下:
异常语句:

SELECT HOU.name ou_name ,fnd_flex_xml_publisher_apis.process_kff_combination_1('FLEX_SELECT_ALL', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') accounts ,AI.invoice_currency_code transaction_currency ,AS1.vendor_name supplier_name ,AI.invoice_id invoice_id_tag ,AI.invoice_num invoice_number ,AI.invoice_date invoice_date ,NVL(AI.exchange_rate,1) inv_exch_rate ,NVL(AI.doc_sequence_value,AI.voucher_num) internal_inv_number ,AI.Invoice_type_lookup_code transaction_type ,NVL(DECODE( :p_exchange_rate_type, 'User', REPLACE(:p_exchange_rate,',','.') , ap_open_bal_rev_rpt_pkg.exch_rate_calc( AI.invoice_currency_code ) ),0) exchange_rate ,DECODE(AI.Invoice_type_lookup_code,'PREPAYMENT' ,-1*(NVL(SUM(NVL(XdL.unrounded_entered_cr,0))-SUM(NVL(XdL.unrounded_entered_dr,0)),0)) ,NVL(SUM(NVL(XdL.unrounded_entered_cr,0))-SUM(NVL(XdL.unrounded_entered_dr,0)),0)) invoice_amt_entered ,DECODE(AI.Invoice_type_lookup_code,'PREPAYMENT' ,-1*(NVL(SUM(NVL(XdL.unrounded_accounted_cr,0))-SUM(NVL(XdL.unrounded_accounted_dr,0)),0)) ,NVL(SUM(NVL(XdL.unrounded_accounted_cr,0))-SUM(NVL(XdL.unrounded_accounted_dr,0)),0)) invoice_amt_accounted FROM ap_invoices AI ,ap_invoice_distributions AID ,hr_operating_units HOU ,ap_suppliers AS1 ,xla_distribution_links XDL ,xla_ae_lines XAL ,gl_import_references GIR ,gl_je_headers GJH ,gl_code_combinations GCC WHERE AS1.vendor_id =AI.vendor_id AND AID.invoice_id =AI.invoice_id AND AI.payment_status_flag <>'Y' --bug7581755 AND HOU.organization_id =AI.org_id AND XDL.event_id =AID.accounting_event_id AND XDL.source_distribution_id_num_1 = AID.invoice_distribution_id AND XDL.application_id =200 --AND XDL.rounding_class_code = 'LIABILITY' AND XDL.rounding_class_code = DECODE(ai.Invoice_type_lookup_code ,'PREPAYMENT', 'PREPAID_EXPENSE','LIABILITY') AND XAL.ae_header_id =XDL.ae_header_id AND XAL.ae_line_num =XDL.ae_line_num AND XAL.ledger_id =AI.set_of_books_id AND XAL.application_id =200 --AND XAL.accounting_class_code ='LIABILITY' AND GCC.code_combination_id =XAL.code_combination_id AND GIR.gl_sl_link_id =XAL.gl_sl_link_id AND GIR.gl_sl_link_table =XAL.gl_sl_link_table AND GJH.je_header_id =GIR.je_header_id AND GJH.status ='P' AND GJH.ledger_id = 2161 AND XAL.ACCOUNTING_DATE <= :P_AS_OF_DATE AND ai.org_id=:p_org_id AND ai.invoice_currency_code = :P_CURRENCY AND ai.invoice_currency_code <> :gc_func_currency AND 1 = 1 GROUP BY HOU.name ,fnd_flex_xml_publisher_apis.process_kff_combination_1('FLEX_SELECT_ALL', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ,AI.invoice_currency_code ,AS1.vendor_name ,AI.invoice_id ,AI.invoice_num ,AI.invoice_date ,AI.Invoice_type_lookup_code ,NVL(AI.doc_sequence_value,AI.voucher_num) ,AI.exchange_rate ,DECODE( :p_exchange_rate_type, 'User', REPLACE(:p_exchange_rate,',','.') , ap_open_bal_rev_rpt_pkg.exch_rate_calc( AI.invoice_currency_code ) ) ORDER BY 1,2,3,5
3,查看表空间使用率
查看表空间使用率的sql语句:

select * from ( Select a.tablespace_name, to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes, to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes, to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024, '99,999.999') use_bytes, to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union all select c.tablespace_name, to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes, to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes, to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes, to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name ) order by tablespace_name
4、问题很奇怪,临时表空间使用率较低,初步判断应该是资源太集中运行导致临时空间太满了
5、查看普通数据文件是否扩展
select d.file_name, d.tablespace_name, d.autoextensible from dba_data_files d
6、查看临时表空间是否可以扩展:
select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
7,增加数据文件解决问题
问了同事,写这个sql语句的小伙伴已经离职半年了,所以无人懂这个复杂的sql的业务逻辑了,暂时优化sql的建议是无法去做了。采用另外一种发难,直接添加一个新的临时表空间的数据文件,设置大一些,设置成4g:
-- 执行添加临时表空间的数据文件命令: ALTERTABLESPACE TEMP ADDTEMPFILE'/home/oradata/powerdes/temp05.dbf' SIZE4G AUTOEXTENDON NEXT128M;
6,临时表空间相关
查看使用消耗临时表空间资源比较多的sql语句:
SELECT se.username, se.sid, su.extents, su.blocks * to_number(rtrim(p.value)) asSpace, tablespace, segtype, sql_text FROM v$sort_usage su, v$parameter p, v$session se, v$sql s WHERE p.name = 'db_block_size' AND su.session_addr = se.saddr AND s.hash_value = su.sqlhash AND s.address = su.sqladdr ORDER BY se.username, se.sid;
增加数据文件
当临时表空间太小时,就需要扩展临时表空间(添加数据文件、增大数据文件、设置文件自动扩展);有时候需要将临时数据文件分布到不同的磁盘分区中,提升IO性能,也需要通过删除、增加临时表空间数据文件。
ALTERTABLESPACE TEMP ADDTEMPFILE'/home/oradata/powerdes/temp05.dbf' SIZE4G AUTOEXTENDON NEXT128M;
删除数据文件
例如,我想删除临时表空间下的某个文件,那么我们有两种方式删除临时表空间的数据文件。
方法1:
alter tablespace temp drop tempfile'/home/oradata/powerdes/temp03.dbf' ;
# 这个方法会删除物理文件
[oracle@pldb1 ~]$ ll /home/oradata/powerdes/temp03.dbf
ls: cannot access /home/oradata/powerdes/temp03.dbf: No such file or directory
方法2:
alter database tempfile'/home/oradata/powerdes/temp04.dbf'drop including datafiles;
注意:删除临时表空间的临时数据文件时,不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件,否则需要手工删除物理文件。
调整文件大小
如下例子,需要将临时数据文件从128M大小调整为256M
alter database tempfile'/home/oradata/powerdes/temp02.dbf'resize 256M;
SQL>
文件脱机联机
-- 脱机
alter database tempfile'/home/oradata/powerdes/temp02.dbf'offline;
-- 联机
alter database tempfile'/home/oradata/powerdes/temp02.dbf'online;
收缩临时表空间
排序等操作使用的临时段,使用完成后会被标记为空闲,表示可以重用,占用的空间不会立即释放,有时候临时表空间会变得非常大,此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。
SQL> ALTERTABLESPACE TEMP SHRINKSPACEKEEP8G;
SQL> ALTERTABLESPACE TEMP SHRINKTEMPFILE'/home/oradata/powerdes/temp05.dbf'
本文来自博客园,作者:Iven_lin,转载请注明原文链接:https://www.cnblogs.com/ivenlin/p/18125756
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步