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
View Code
复制代码

 

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
View Code
复制代码

 

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'

 

posted @   Iven_lin  阅读(2145)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示