ORACLE 臨時表空間使用率過高的原因及解決方案

由于臨時表空間使用率過高，達(dá)到了百分之百，雖然沒有任何的報錯，但存在一定的隱患和告警信息，有待解決問題。由于臨時表空間主要使用在以下幾種情況:1、orderbyorgroupby（discsort占主要部分）；2、索引的創(chuàng)建和重創(chuàng)建；3、distinct操作；4、union&intersect&minussort-mergejoins；5、Analyze操作；6、有些異常也會引起TEMP的暴漲。通過查詢相關(guān)的資料，發(fā)現(xiàn)解決方案有如下幾種：一、重建臨時表空間tempTemporarytablespace是不能直接drop默認(rèn)的臨時表空間的，不過我們可以通過以下方法達(dá)到。查看目前的TemporaryTablespaceSQL>selectnamefromv$tempfile;NAME/dev/md/vg_yx_ora1/rdsk/d101SQL>selectusername,temporary_tablespacefromdba_users;USERNAMETEMPORARY_TABLESPACESYSSYSTEMSYSSYSTEMDBSNMPJIFENAIDBAIZZDBUNIONMONTESTAISTATAILKSELECTAIMONTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPUSERNAMETEMPORARY_TABLESPACEUNIONMONSTEMPACCOUNTTEMPOFFICETEMPZZDBTEMPAIGDBTEMPPERFSTATTEMPOUTLNTEMPWMSYSTEMP創(chuàng)建中轉(zhuǎn)臨時表空間createtemporarytablespaceTEMPItempfile'/dev/md/vg_yx_ora1/rdsk/d1017'SIZE4089M;改變?nèi)笔∨R時表空間為剛剛創(chuàng)建的新臨時表空間temp1alterdatabasedefaulttemporarytablespacetemp1;3?刪除原來臨時表空間droptablespacetempincludingcontentsanddatafiles;重新創(chuàng)建臨時表空間createtemporarytablespaceTEMPtempfile'/dev/md/vg_yx_ora1/rdsk/d1016'SIZE4089M;重置缺省臨時表空間為新建的temp表空間alterdatabasedefaulttemporarytablespacetemp;刪除中轉(zhuǎn)用臨時表空間droptablespacetemp1includingcontentsanddatafiles;以上的方法只是暫時釋放了臨時表空間的磁盤占用空間，是治標(biāo)但不是治本的方法，真正的治本的方法是找出數(shù)據(jù)庫中消耗資源比較大的sql語句，然后對其進(jìn)行優(yōu)化處理。下面是查詢在sort排序區(qū)使用的執(zhí)行耗時的SQL：Selectse.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))asSpace,tablespace,segtype,sql_textfromv$sort_usagesu,v$parameterp,v$sessionse,v$二'db_block_size'andsu.session_addr二se.saddrands.hash_value二su.sqlhashands.address二su.sqladdrorderbyse.username,se.sid;或是：Selectsu.username,su.Extents,tablespace,segtype,sql_textfromv$sort_usagesu,v$sqlsWheresu.SQL_ID=s.SQL_ID;注：如果原臨時表空間無用戶使用(selecttablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocksfromv$sort_segment;),如果是文件系統(tǒng)可以看看文件的時間戳。二、修改參數(shù)(這個方案緊適用于8i及8i以下的版本)修改一下TEMP表空間的storage參數(shù)，讓Smon進(jìn)程觀注一下臨時段，從而達(dá)到清理和TEMP表空間的目的。SQL>altertablespacetempincrease1;SQL>altertablespacetempincrease0;三、Killsession1、使用如下語句a查看一下認(rèn)誰在用臨時段SELECTse.username,se.SID,se.serial#,se.sql_address,se.machine,gram,su.TABLESPACE,su.segtype,su.CONTENTSfromv$sessionse,v$sort_usagesuWHEREse.saddr=su.session_addr2、kill正在使用臨時段的進(jìn)程SQL>Altersystemkillsession'sid,serial#';3、把TEMP表空間回縮一下SQL>AltertablespaceTEMPcoalesce;注：這處方法只能針對字典管理表空間(DietionaryManagedTablespace)。于本地管理表空間(LMT:LocalManagedTablespace)，不需要整理的。9i以后只能創(chuàng)建本地管理的表空間。四、查詢占用TEMP的SQL語句進(jìn)行優(yōu)化SQL>selecttextfromdba_viewswhereview_name='SM$TS_FREE';TEXTselecttablespace_name,sum(bytes)bytesfromdba_free_spacegroupbytables查詢誰在使用臨時表空間：selectuser,tablespace,blocksfromv$sort_usageorderbyblocks;哪個語句在使用臨時表空間:selectsess.username,sql.sql_text,sort.blocksfromv$sessionsess,v$sqltextsql,v$sort_usagesortwheresess.serial#=sort.session_numandsort.sqladdr二sql.addressandsort.sqlhash=sql.hash_valueandsort.blocks>200;或者:Selectse.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))asSpace,tablespace,segtype,sql_textfromv$sort_usagesu,v$parameterp,v$sessionse,v$二'db_block_si