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：
SQL> altertablespace temp droptempfile'/home/oradata/powerdes/temp03.dbf' ;
Tablespace altered.
SQL>
# 這個方法會刪除物理檔案
[oracle@pldb1 ~]$ ll /home/oradata/powerdes/temp03.dbf
ls: cannot access /home/oradata/powerdes/temp03.dbf: No such file or directory
[oracle@pldb1 ~]$
方法2：
SQL> alterdatabasetempfile'/home/oradata/powerdes/temp04.dbf'dropincludingdatafiles;
Database altered.
SQL>
注意：刪除臨時表空間的臨時資料檔案時，不需要指定INCLUDING DATAFILES 選項也會真正刪除物理檔案，否則需要手工刪除物理檔案。
調整檔案大小
如下例子，需要將臨時資料檔案從128M大小調整為256M
SQL> alterdatabasetempfile'/home/oradata/powerdes/temp02.dbf'resize256M;
Database altered.
SQL>
檔案離線聯機
-- 離線
alterdatabasetempfile'/home/oradata/powerdes/temp02.dbf'offline;
-- 聯機
alterdatabasetempfile'/home/oradata/powerdes/temp02.dbf'online;
收縮臨時表空間
排序等操作使用的臨時段，使用完成後會被標記為空閒，表示可以重用，佔用的空間不會立即釋放，有時候臨時表空間會變得非常大，此時可以透過收縮臨時表空間來釋放沒有使用的空間。收縮臨時表空間是ORACLE 11g新增的功能。
SQL> ALTERTABLESPACE TEMP SHRINKSPACEKEEP8G;
SQL> ALTERTABLESPACE TEMP SHRINKTEMPFILE'/home/oradata/powerdes/temp05.dbf'