解決ora-01652無法通過128（在temp表空間中）擴展temp段的過程解決ora-01652無法通過128（在temp表空間中）擴展temp段的過程
昨天開發(fā)人員跟我說,，執(zhí)行一個sql語句后，大約花了10分鐘,，好不容易有一個結(jié)果,，但是報了一個ora-01652錯誤，查閱了oracle的錯誤代碼說明：意思是指temp表空間無法自動擴展temp段,。這種問題一般有兩種原因：一是臨時表空間空間太小,，二是不能自動擴展。 分析過程：
既然是temp表空間有問題,，那當然就要從temp表空間說起啦,。首先要說明的是temp表空間的作用，temp表空間主要是用作需要排序的操作,。
1.臨時表空間 是用于在進行排序操作(如大型查詢,創(chuàng)建索引和聯(lián)合查詢期間存儲臨時數(shù)據(jù))每個用戶都有一個臨時表空間,。
2.對于大型操作頻繁,(大型查詢,大型分類查詢,大型統(tǒng)計分析等),應(yīng)指定單獨的臨時表空間,以方便管理,。 3.分配用戶單獨臨時表空間,一般是針對 大型產(chǎn)品數(shù)據(jù)庫,OLTP數(shù)據(jù)庫,數(shù)據(jù)庫倉庫對于小型產(chǎn)品不需要單獨制定臨時表空間,使用默認臨時表空間。 正常情況下,，一個sql執(zhí)行之后,，返回結(jié)果后系統(tǒng)會自動收回分配給這個用戶的空間。以便可以把此部分空間再分配給其他用戶,。 好了,，既然問題出在臨時表空間，那就查看一下臨時表空間信息：
select * from dba_tablespaces;
select * from dba_temp_files; select * from v$tempfile 通過觀察發(fā)現(xiàn),，temp表空間只有一個數(shù)據(jù)文件,，大小為4G，不自動擴展,。大小為4G,，應(yīng)該說是不能算小啦。這時想到,，需要看一看執(zhí)行的sql語句到底是什么樣的,，難道它能把這個空間用完（由于系統(tǒng)事務(wù)非常少，所以暫不考慮其它用戶語句對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 咋一看，真不賴,，字段將近一百個,而且原表記錄數(shù)有15000多條。所以可能真的把臨時表空間吃完,。于是對涉及到的基表做了一個空間占用情況統(tǒng)計：
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字節(jié),，大約是19M。還不包括其它的表的字段統(tǒng)計,。先將temp數(shù)據(jù)文件自動擴展,。先不管這個sql語句是否優(yōu)化，做一個執(zhí)行計劃和統(tǒng)計分析,，得到如下結(jié)果：
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。這當然會把臨時表空間全部吃掉,。這時應(yīng)該從sql語句本身來找問題,。
那個sql語句基表大約有15000，而且涉及將近100個字段,，最后還有一個非常耗費資源的排序操作,。去掉那個order by 查看記錄的返回數(shù)。 發(fā)現(xiàn)返回的記錄數(shù)為26950729,。這對我們數(shù)據(jù)庫的硬件配置來說是一個很大數(shù)量級的數(shù)據(jù),，而且字段有非常多,，所以占用既定的temp表空間的之后還不停的擴展，如果沒有設(shè)置自動擴展,，必然導(dǎo)致無法分配temp段,。所以那個問題的產(chǎn)生就是必然的啦。現(xiàn)在追究為什么基表只有15000多條,，而查詢結(jié)構(gòu)卻是26950729條,，所以懷疑是sql語句中存在cartesian，于是開始從業(yè)務(wù)需求來分析這個語句,，果然是將最后一個(SELECT taskid,reexam FROM ENROL_EXAMINEE) b 中的基表應(yīng)該是enrol_task而不是enrol_examinee,，因為會產(chǎn)生兩個表同個taskid來連接，而每個taskid下有很多學(xué)生,，這就導(dǎo)致產(chǎn)生了一個龐大的cartesian乘積,。最終導(dǎo)致對26950729條記錄的排序而使臨時表空間用盡。（此時沒有自動擴展）將b結(jié)果集中ENROL_EXAMINEE用ENROL_TASK來替換,，即使還有一個排序,，結(jié)果也會在很短的時間內(nèi)返回。 最后需要說明的是：
1,、sql語句完成之后,，需要檢查記錄的準確性。 2,、盡量不要在視圖中進行order by ,，這是一個非常耗費資源的操作。 寫下拉,，以存警示,！
=======================================================================
ORA-01652:unable to extend temp segment by num in tablespace name 產(chǎn)生原因：ORACLE臨時段表空間不足，因為ORACLE總是盡量分配連續(xù)空間,，一但沒有足夠的可分配空間或者分配不連續(xù)就會出現(xiàn)上述的現(xiàn)象,。 解決方法：我們知道由于ORACLE將表空間作為邏輯結(jié)構(gòu)-單元，而表空間的物理結(jié)構(gòu)是數(shù)據(jù)文件,，數(shù)據(jù)文件在磁盤上物理地創(chuàng)建,，表空間的所有對象也存在于磁盤上，為了給表空間增加空間,，就必須增加數(shù)據(jù)文件,。先查看一下指定表空間的可用空間，使用視圖SYS.DBA_FREE_SPACE,，視圖中每條記錄代表可用空間的碎片大?。?/FONT> SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=’’; 返回的信息可初步確定可用空間的最大塊，看一下它是否小于錯誤信息中提到的尺寸,，再查看一下缺省的表空間參數(shù)： 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); 適當增大缺省值的大小有可能解決出現(xiàn)的錯誤問題,，也可以通過修改用戶的臨時表空間大小來解決這個問題： SQL>ALTER USER username TEMPORARY TABLESPACE new_tablespace_name; 使用ALTER TABLESPACE命令,，一但完成，所增加的空間就可使用,，無需退出數(shù)據(jù)庫或使表空間脫機,，但要注意，一旦添加了數(shù)據(jù)文件,，就不能再刪除它,，若要刪除，就要刪除表空間,。 一個報錯例子如下： ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACE 相應(yīng)的英文如下： 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. |
|
|
