当排序操作重建索引等大型操作无法在内存中完成时临时表空间将为排序提供便利一般情况下临时表空间为多个用户多个会话所共享不能为会话分批空间配额临时表空间耗用过度且在不能自动扩展的情形下将收到ORAunable to extend temp segment 错误下面
描述了过度扩展后如何释放临时表空间
一临时表空间何时释放
检索数据的会话游标关闭时占用的临时空间即被释放
数据库关闭重启（一般情况）会话 log off
二释放过大的临时表空间
<span > 查看当前临时表空间的情况SQL> select * from v$version where rownum<
BANNER
Oracle Database g Release bit Production SQL> @temp_sort_segment
+==================================================================================+ | Segment Name The segment name is a concatenation of the | | SEGMENT_FILE （File number of the first extent） | | and the | | SEGMENT_BLOCK （Block number of the first extent） | | Current Users Number of active users of the segment | | Total Temp Segment Size Total size of the temporary segment in MB | | Currently Used Bytes Bytes allocated to active sorts | | Extent Hits Number of times an unused extent was found in the pool | | Max Size Maximum number of MB ever used | | Max Used Size Maximum number of MB used by all sorts | | Max Sort Size Maximum number of MB used by an individual sort | | Free Requests Number of requests to deallocate | +==================================================================================+——>此时临时表空间go_temp中达到了GB Tablespace Segment Current Currently Pct Extent Max Max Used Max Sort Free Name Name Users Used MB Used Hits Size MB Size MB Size MB Requests
TEMP SYS GO_TEMP SYS ************** —— —— —— —— —— —— ——sum
SQL> col tbsname format a SQL> select sname tbsnametname（tbytes//） mbtstatus from v$tablespace sv$tempfile t where sts# = tts#
TBSNAME NAME MB STATUS
TEMP /u/database/ORADB/temp/tempORADBdbf ONLINE GO_TEMP /u/database/ORADB/temp/ORADB_tempORADBdbf ONLINE
SQL> @temp_usage ——>此时temp已使用的为MB而GO_TEMP未使用
TABLESPACE MB_TOTAL MB_USED MB_FREE
GO_TEMP TEMP
观察及分析临时表空间的耗用情况SQL> select count（*） from big_table ——>开启另一个session
COUNT（*）
SQL> select * from big_table order by desc ——>对big_table 实施排序
SQL> alter index pk_stock_tbl_arc rebuild ——>开启另一个session重建索引
SQL> @temp_sort_segmentsql ——>可以看到此时temp表空间耗用达到MBgo_temp的耗用达到MB
Tablespace Segment Current Currently Pct Extent Max Max Used Max Sort Free Name Name Users Used MB Used Hits Size MB Size MB Size MB Requests
TEMP SYS GO_TEMP SYS ************** —— —— —— —— —— —— ——sum
SQL> @temp_sort_userssql ——>获得当前排序的会话
INST_ID SID_SERIAL Username OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS
SCOTT oracle SQL*Plus oracle@SZD TEMP B （TNS V V）
GO_ADMIN oracle SQL*Plus oracle@SZD GO_TEMP B （TNS V V）
使用resize缩小临时表空间如不能缩小转到下一步SQL> SELECT alter database tempfile || aname || resize || bsiz || M resize_command FROM v$tempfile a （SELECT ceil（tmsizemaxblk * bkvalue / / ） siz FROM （SELECT nvl（MAX（segblk#） ） maxblk FROM v$sort_usage） tmsize （SELECT VALUE FROM v$parameter WHERE NAME = db_block_size） bk） b
RESIZE_COMMAND
alter database tempfile /u/database/ORADB/temp/ORADB_tempORADBdbf resize Malter database tempfile /u/database/ORADB/temp/tempORADBdbf resize M
——>实际上此时占用GB的临时数据文件已经缩小alter database tempfile /u/database/ORADB/temp/ORADB_tempORADBdbf resize M
Database altered
——>为便于演示此时假定TEMP为过大的临时表空间且不能释放——>下面调整表明已使用空间超出了分配的空间SQL> alter database tempfile /u/database/ORADB/temp/tempORADBdbf resize Malter database tempfile /u/database/ORADB/temp/tempORADBdbf resize M * ERROR at line ORA file contains used data beyond requested RESIZE value
SQL> select count（*） from v$sort_usage where tablespace=TEMP ——>当前有未释放的临时段
COUNT（*）
/**************************************************/ /* Author Robinson Cheng */ /* Blog ; */ /* MSN robin */ /* QQ */ /**************************************************/
新建一个中转临时表空间SQL> create temporary tablespace temp tempfile /u/database/ORADB/temp/ORADB_tempdbf size m autoextend on
Tablespace created
——>如果此时过大的临时表空间为缺省的临时表空间则必须将缺省的临时表空间设置为新的临时表空间之后SQL> select property_nameproperty_value from database_properties where property_name like DEFAULT_TEMP_TABLESPACE
PROPERTY_NAME PROPERTY_VALUE
DEFAULT_TEMP_TABLESPACE TEMP
SQL> alter database default temporary tablespace temp
Database altered
转移用户到中转临时表空间——>过大临时表空间上的那些用户需要迁移到新建的临时表空间——>查询dba_users视图查询哪些用户位于过大的临时表空间之上——>并使用下面的命令将其切换到新的临时表空间alter user <username> temporary tablespace temp
等到过大临时表空间上的没有临时段被使用即已经全部释放即可删除过大的临时表空间
SQL> show user ——>由于当前用户为scott所以临时表空间未能释放USER is SCOTT
SQL> conn / as sysdba ——>切换到sysdba Connected
SQL> @temp_usage ——>临时段已经被释放
TABLESPACE MB_TOTAL MB_USED MB_FREE
GO_TEMP TEMP
——>如果没有释放在可以kill session的情况下kill session利用前面获得的sidserial#来执行（前提是允许该情况发生）
alter system kill session
删除过大的临时表空间
SQL> alter tablespace temp tempfile offline ——>先将其脱机
Tablespace altered
SQL> drop tablespace temp including contents and datafiles ——>删除临时表空间及相应的文件
Tablespace dropped
SQL> select sname tbsnametname（tbytes//） mbtstatus from v$tablespace sv$tempfile t where sts# = tts#
TBSNAME NAME MB STATUS
GO_TEMP /u/database/ORADB/temp/ORADB_tempORADBdbf ONLINE TEMP /u/database/ORADB/temp/ORADB_tempdbf ONLINE
——>也可以使用下面的命令来完成仅仅删除单个文件ALTER DATABASE TEMPFILE /u/database/ORADB/temp/tempORADBdbf DROP INCLUDING DATAFILES ——>删除单个文件
根据需求可以创建原来的临时表空间并将切换出去用户切换到此临时表空间</span>
三总结
关注alert_<sid>log文件中的ORA错误并调查什么原因导致该错误有些时候并不是由于当前的SQL 导致临时表空间不能扩展很可能由于前一个SQL耗用了%的临时表空间而后一个SQL执行时即出现错误对于此类情况应调查前一SQL并调整避免过多的磁盘排序
如果基于空间压力应该关闭临时表空间的自动扩展因此为临时表空间设定合理的大小就成了一个问题个人的解决方案是首先检查ORA其次是观察业务高峰期的峰值如前面查询中的字段Max Size（ Maximum number of MB ever used）的值来预估如果大师们有更好的建议不妨拍砖
通过重启数据库临时表空间所耗用的大小有时候并不能缩小
在Oracle g之前一般是通过创建中转临时表空间来达到缩小的目的不是很完美因为有些时候临时段未释放导致不能删除临时表空间及数据文件在g可以直接使用下面的命令来完成
alter tablespace temp shrink space
alter tablespace temp shrink tempfile <dir> keep n <mb/kb>
系统缺省的临时表空间不能被删除因此如果系统缺省的临时表空间过大删除前应该新置一个系统缺省的临时表空间
删除过大临时表空间前其上的用户应该先将其设定到中转临时表空间重建后再将其置回原状态
减少磁盘排序的首要任务调整SQL如避免笛卡尔积为表添加合理的索引等其次要考虑PGA的值是否设定合理
