解決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 ，這是一個非常耗費資源的操作。

寫下拉，以存警示！

=======================================================================

以下摘自http://blog.csdn.net/luojunjing/archive/2005/02/12/286327.aspx

ORA-01652:unable to extend temp segment by num in tablespace name

產生原因：ORACLE臨時段表空間不足，因為ORACLE總是儘量分配連續空間，一但沒有足夠的可分配空間或者分配不連續就會出現上述的現象。

解決方法：我們知道由於ORACLE將表空間作為邏輯結構-單元，而表空間的物理結構是資料檔案，資料檔案在磁碟上物理地建立，表空間的所有物件也存在於磁碟上，為了給表空間增加空間，就必須增加資料檔案。先檢視一下指定表空間的可用空間，使用檢視SYS.DBA_FREE_SPACE，檢視中每條記錄代表可用空間的碎片大小：

SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=’’;

返回的資訊可初步確定可用空間的最大塊，看一下它是否小於錯誤資訊中提到的尺寸，再檢視一下預設的表空間引數：

SQL>SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=name;

通過下面的SQL命令修改臨時段表空間的預設儲存值：

SQL>ALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY);

適當增大預設值的大小有可能解決出現的錯誤問題，也可以通過修改使用者的臨時表空間大小來解決這個問題：

SQL>ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;

使用ALTER TABLESPACE命令，一但完成，所增加的空間就可使用，無需退出資料庫或使表空間離線，但要注意，一旦新增了資料檔案，就不能再刪除它，若要刪除，就要刪除表空間。

一個報錯例子如下：

ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACE

相應的英文如下：

Cause: Failed to allocate extent for temp segment in tablespace

Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace or create the object in another tablespace.

 

補充：

重建Temp表空間 

1. startup --啟動資料庫      
2.      
3. create temporary tablespace TEMP2 TEMPFILE '/home2/oracle/oradata/sysmon/temp02.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --建立中轉臨時表空間      
4.      
5. alter database default temporary tablespace temp2; --改變預設臨時表空間 為剛剛建立的新臨時表空間temp2      
6.      
7. drop tablespace temp including contents and datafiles;--刪除原來臨時表空間      
8.      
9. create temporary tablespace TEMP TEMPFILE '/home2/oracle/oradata/sysmon/temp01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新建立臨時表空間      
10.      
11. alter database default temporary tablespace temp; --重置預設臨時表空間為新建的temp表空間      
12.      
13. drop tablespace temp2 including contents and datafiles;--刪除中轉用臨時表空間      
14.      
15. alter user roll temporary tablespace temp; --重新指定使用者表空間為重建的臨時表空間