解決ora-01652無法透過128（在temp表空間中）擴充套件temp段的過程

解決ora-01652無 法透過128（在temp表空間中）擴充套件temp段的過程
 
昨天開發人員跟我說，執行一個sql語句後，大約花了10分鐘，好不容易有一 個結果，但是報了一個ora-01652錯誤，查閱了oracle的錯誤程式碼說明：意思是指temp表空間無法自動擴充套件temp段。這種問題一般有兩種原 因：一是臨時表空間空間太小，二是不能自動擴充套件。

分析過程：

   既然是temp表空間有問題，那當然就要從temp表空間說起啦。首先要說明的是temp表空間的作用，temp表空間主要是用作需要排序的操作。

   1.臨時表空間 是用於在進行排序操作(如大型查詢,建立索引和聯合查詢期間儲存臨時資料)每個使用者都有一個臨時表空間。
   2.對於大型操作頻繁,(大型查詢,大型分類查詢,大型統計分析等),應指定單獨的臨時表空間,以方便管理。
   3.分配使用者單獨臨時表空間,一般是針對 大型產品資料庫,OLTP資料庫,資料庫倉庫對於小型產品不需要單獨制定臨時表空間,使用預設臨時表空間。
   正常情況下，一個sql執行之後，返回結果後系統會自動收回分配給這個使用者的空間。以便可以把此部分空間再分配給其他使用者。

好了，既然問題出在臨時表空間，那就檢視一下臨時表空間資訊：

   select * from dba_tablespaces;
   select * from dba_temp_files;
   select * from v$tempfile

   透過觀察發現，temp表空間只有一個資料檔案，大小為4G，不自動擴充套件。大小為4G，應該說是不能算小啦。這時想到，需要看一看執行的sql語句到底是 什麼樣的，難道它能把這個空間用完（由於系統事務非常少，所以暫不考慮其它使用者語句對temp的影響），於是就把那個語句複製下拉，研究一番：

SELECT a.TASKID, a.EXAMTYPECODE, a.CONTROLDEPARTMENT, a.DEFAULTRECRUITSPEC, a.ISUPCUNTRYLINE,
 a.ISUPCOLLEGELINE, a.ISMAYJOINRETRIAL, a.JOINRETRIALGIST, a.JOINRETRIALBAK, a.TEMPERSIGN,
 a.RECRUITSTUDENTTYPE, a.MATRICULATEGIST, a.ISNOTICEEXAMINEE, a.AFTERHANDLEDEP, a.TUITION,
 a.KSBH, a.BH, a.XXB, a.BMDDM, a.BMDMC,
 a.BMH, a.XM, a.XMPY, a.ZJLX, a.ZJLXMC,
 a.ZJHM, a.CSRQ, a.MZM, a.MZ, a.XBM,
 a.XB, a.HFM, a.HF, a.ZZMMM, a.ZZMM,
 a.HKSZSSM, a.HKSZSSMC, a.HKSZDXXDZ, a.CSDSSM, a.CSDSSMC,
 a.CSDXXDZ, a.XXGZDW, a.TXDZ, a.YZBM, a.LXDH,
 a.DZXX, a.BYNY, a.XLM, a.XL, a.XWM,
 a.XW, a.KSLYM, a.KSLY, a.KSFSM, a.KSFS,
 a.BKLBM, a.BKLB, a.DASZDWSSM, a.DASZDWSSMC, a.DASZDW,
 a.DASZDWDZ, a.DASZDWYZBM, a.XXGZJL, a.BYXX, a.BYXX1,
 a.BYXX2, a.BYXX3, a.BYXX4, a.JL, a.JTCY,
 a.BYDW, a.BYDWM, a.BYZYDM, a.BYZYMC, '0'||a.DWDM DWDM,
 a.DWMC, a.TJDWDM, a.TJDWMC, a.ZYDM, a.ZYMC,
 a.YXSM, a.YXSMC, a.YJFXM, a.YJFX, a.DSXM,
 a.ZZLLM, a.ZZLLMC, a.WGYM, a.WGYMC, a.YWK1M,
 a.YWK1MC, a.YWK2M, a.YWK2MC, a.YWK3M, a.YWK3MC,
 a.XYJRM, a.XYJR, a.JFBZ, a.ZXBZ, a.BMSJ,
 a.BYXXBZ, a.SFZH, a.BYND, a.KSDW, a.DWSZSSM,
 a.DWSZSSMC, a.ZZLL, a.WGY, a.YWK1, a.YWK2,
 a.YWK3, a.ZF, (NVL(a.fscj,0)) FSCJ, a.LQLBM, a.LQLB,
 a.DXWPDW, a.PG, a.PGBZ, a.BZ, a.WDDWLM,
 a.WDDWL, a.JFLY, a.LQQK, a.BLDW, a.NRXNY,
 a.LQND, a.BZ1, a.BZ2, a.KCH, a.KCDD,
 a.ZWH,a.ZPPATH, a.XH, a.BKZYM, a.BKZY,
 a.XVH,a.DY6FLAG, a.BXYWK1MC, a.BXYWK2MC, a.BXYWK3MC,
 a.QKBZ1, a.QKBZ2, a.QKBZ3, a.QKBZ4, a.QKBZ5,
 a.SSMZLQ, a.KSQK, a.JGM, a.JGS, a.JGSX,
 a.NLDM, a.NL, a.DXZYM, a.DXZY, a.HKSZDSX,
 a.DQDM, a.YDWYB, a.BRTXZ, a.GZDWZ, a.YDWLM,
 a.DWLB, a.BRYB, a.BRDH, a.JTDWM, a.GL,
 a.TSLB, a.TSLBM, a.XXFSM, a.XXFS, a.LQZYM,
 LQZY, ZPCJ, DSXHMH1, DSXHMH2, DSXHMH3,
 a.DSXHMH4, a.SZSSM, a.ZXJH, a.ZXJHBZ, a.BLZGNX,
 BLZG, BLZG1, BLZBZ, JS1MC, JS1CJ,
 a.JS2MC, a.JS2CJ, a.SSMZ, a.SSMZBZ, a.DXWPSSM,
 a.DXWPSS, a.LQXSM, a.LQXSMC, a.LQDSM, a.LQDSXM,
 a.LQYJFXM, a.LQYJFXMC, a.HANDLERESULT, a.XLZSBH, a.XWZSBH,
 a.LQYJFXID, a.XXXS,nvl2(a.reexam , ROUND(GYZF),(zf * ( 1- 0.3))) GYZF,a.GYFS, a.GYCS,
 b.reexam ,a.SPECIALITYNO,a.SPECIALITYNAME,
   (
   SELECT STUDENTTYPE FROM CODE_STUDENTTYPE
   WHERE STUDENTTYPECODE=A.recruitstudenttype ) STUDENTTYPE,
   (SELECT collegename FROM ENROL_COLLEGEINFO)  FSDWMC,
   (SELECT collegecode FROM ENROL_COLLEGEINFO) FSDWDM,
   (CASE WHEN a.handleresult='已錄取' THEN '1' ELSE '0' END ) nlq,'' HKSZDM
FROM
 ( SELECT X.*, Y.SPECIALITYNO,Y.SPECIALITYNAME FROM ENROL_EXAMINEE X,   BASE_SPECIALITY Y WHERE X.DEFAULTRECRUITSPEC=Y.SPECSTANDCODE AND x.CONTROLDEPARTMENT IS NOT NULL  ) A
 ,(SELECT taskid,reexam FROM ENROL_EXAMINEE) b WHERE a.taskid=b.taskid
 ORDER BY A.KSBH ASC

 咋一看，真不賴，欄位將近一百個,而且原表記錄數有15000多條。所以可能真的把臨時表空間吃完。於是對涉及到的基表做了一個空間佔用情況 統計：
SQL> set serveroutput on
SQL> exec show_space('ENROL_EXAMINEE','AUTO');
Total Blocks............................2432                                   
Total Bytes.............................19922944                               
Unused Blocks...........................0                                      
Unused Bytes............................0                                      
Last Used Ext FileId....................11                                     
Last Used Ext BlockId...................301576                                 
Last Used Block.........................128                                    

計算一下基表佔用19922944位元組，大約是19M。還不包括其它的表的欄位統計。先將temp資料檔案自動擴充套件。先不管這個sql語句是否 最佳化，做一個執行計劃和統計分析，得到如下結果：

PLAN_TABLE_OUTPUT                                                              
----------------------------------------------------------------------------------------------------                                    
| Id  | Operation                     |  Name                       | Rows  | Bytes |TempSpc| Cost  |                                   
|   0 | SELECT STATEMENT              |                             |  9023K|  9130M|       |  3579K|                                   
|   1 |  SORT ORDER BY                |                             |  9023K|  9130M|    19G|  3579K|
|*  2 |   HASH JOIN                   |                             |  9023K|  9130M|       |   439 |                                   
|   3 |    TABLE ACCESS FULL          | ENROL_EXAMINEE              | 15146 |   310K|       |   231 |                                   
|*  4 |    TABLE ACCESS BY INDEX ROWID| ENROL_EXAMINEE              |   596 |   591K|       |     2 |                                   
|   5 |     NESTED LOOPS              |                             |  4171 |  4236K|       |   170 |         
|   6 |      TABLE ACCESS FULL        | BASE_SPECIALITY             |    84 |  2016 |       |     2 |       
|*  7 |      INDEX RANGE SCAN         | EXAMINEE_DEFAULTSPEC_INDEX  |   202 |       |       |     1 |          

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - access("SYS_ALIAS_1"."TASKID"="ENROL_EXAMINEE"."TASKID")                
   4 - filter("SYS_ALIAS_1"."CONTROLDEPARTMENT" IS NOT NULL)                   
   7 - access("SYS_ALIAS_1"."DEFAULTRECRUITSPEC"="Y"."SPECSTANDCODE")          

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
       filter("SYS_ALIAS_1"."DEFAULTRECRUITSPEC" IS NOT NULL)                  
                                                                               
Note: cpu costing is off                                          

由此可知，臨時表空間擴充套件到19G。這當然會把臨時表空間全部吃掉。這時應該從sql語句本身來找問題。
那個sql語句基表大約有 15000，而且涉及將近100個欄位，最後還有一個非常耗費資源的排序操作。去掉那個order by 檢視記錄的返回數。
發現返回的記錄數為 26950729。這對我們資料庫的硬體配置來說是一個很大數量級的資料，而且欄位有非常多，所以佔用既定的temp表空間的之後還不停的擴充套件，如果沒有 設定自動擴充套件，必然導致無法分配temp段。所以那個問題的產生就是必然的啦。現在追究為什麼基表只有15000多條，而查詢結構卻是26950729 條，所以懷疑是sql語句中存在cartesian，於是開始從業務需求來分析這個語句，果然是將最後一個(SELECT taskid,reexam FROM ENROL_EXAMINEE) b 中的基表應該是enrol_task而不是enrol_examinee，因為會產生兩個表同個taskid來連線，而每個taskid下有很多學生，這 就導致產生了一個龐大的cartesian乘積。最終導致對26950729條記錄的排序而使臨時表空間用盡。（此時沒有自動擴充套件）將b結果集中 ENROL_EXAMINEE用ENROL_TASK來替換，即使還有一個排序，結果也會在很短的時間內返回。

最後需要說明的是：
    1、sql語句完成之後，需要檢查記錄的準確性。
    2、儘量不要在檢視中進行order by ，這是一個非常耗費資源的操作。

寫下拉，以存警示！