ORACLE 临时表空间使用率过高的原因及解决方案

由于临时表空间使用率过高，达到了百分之百，虽然没有任何的报错，但存在一定的隐患和告警信息，有待解决问题。由于临时表空间主要使用在以下几种情况:1、orderbyorgroupby（discsort占主要部分）；2、索引的创建和重创建；3、distinct操作；4、union&intersect&minussort-mergejoins；5、Analyze操作；6、有些异常也会引起TEMP的暴涨。通过查询相关的资料，发现解决方案有如下几种：一、重建临时表空间tempTemporarytablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法达到。查看目前的TemporaryTablespaceSQL>selectnamefromv$tempfile;NAME/dev/md/vg_yx_ora1/rdsk/d101SQL>selectusername,temporary_tablespacefromdba_users;USERNAMETEMPORARY_TABLESPACESYSSYSTEMSYSSYSTEMDBSNMPJIFENAIDBAIZZDBUNIONMONTESTAISTATAILKSELECTAIMONTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPTEMPUSERNAMETEMPORARY_TABLESPACEUNIONMONSTEMPACCOUNTTEMPOFFICETEMPZZDBTEMPAIGDBTEMPPERFSTATTEMPOUTLNTEMPWMSYSTEMP创建中转临时表空间createtemporarytablespaceTEMPItempfile'/dev/md/vg_yx_ora1/rdsk/d1017'SIZE4089M;改变缺省临时表空间为刚刚创建的新临时表空间temp1alterdatabasedefaulttemporarytablespacetemp1;3•删除原来临时表空间droptablespacetempincludingcontentsanddatafiles;重新创建临时表空间createtemporarytablespaceTEMPtempfile'/dev/md/vg_yx_ora1/rdsk/d1016'SIZE4089M;重置缺省临时表空间为新建的temp表空间alterdatabasedefaulttemporarytablespacetemp;删除中转用临时表空间droptablespacetemp1includingcontentsanddatafiles;以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正的治本的方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。下面是查询在sort排序区使用的执行耗时的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;),如果是文件系统可以看看文件的时间戳。二、修改参数(这个方案紧适用于8i及8i以下的版本)修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。SQL>altertablespacetempincrease1;SQL>altertablespacetempincrease0;三、Killsession1、使用如下语句a查看一下认谁在用临时段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正在使用临时段的进程SQL>Altersystemkillsession'sid,serial#';3、把TEMP表空间回缩一下SQL>AltertablespaceTEMPcoalesce;注：这处方法只能针对字典管理表空间(DietionaryManagedTablespace)。于本地管理表空间(LMT:LocalManagedTablespace)，不需要整理的。9i以后只能创建本地管理的表空间。四、查询占用TEMP的SQL语句进行优化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