当排序操作、重建索引等大型操作无法在内存中完成时，临时表空间将为排序提供便利。一般情况下临时表空间为多个用户，多个会话所共享。不能为会话分批空间配额。临时表空间耗用过度且在不能自动扩展的情形下将收到“ORA-1652:unable to extend temp segment” 错误.下面描述了过度扩展后如何释放临时表空间。
与之相关的内容参考：
一、临时表空间何时释放
检索数据的会话游标关闭时，占用的临时空间即被释放
数据库关闭，重启(一般情况),会话 log off
二、释放过大的临时表空间
1、查看当前临时表空间的情况
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit 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中达到了32GB
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.0.0 4 4 2 1,864 217 217 217 0
GO_TEMP SYS.0.0 0 0 0 1,305 32,766 367 367 0
************** ------- --------- -------- -------- -------- -------- --------
sum 4 4 3,169 32,983 584 584 0
SQL> col tbsname format a15
SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status
2 from v$tablespace s,v$tempfile t
3 where s.ts# = t.ts#;
TBSNAME NAME MB STATUS
--------------- -------------------------------------------------- ---------- -------
TEMP /u02/database/ORADB/temp/tempORADB.dbf 235 ONLINE
GO_TEMP /u02/database/ORADB/temp/ORADB_tempORADB.dbf 32767 ONLINE
SQL> @temp_usage2 -->此时temp已使用的为4MB，而GO_TEMP未使用
TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
GO_TEMP 32767 0 32767
TEMP 218 4 214
2、观察及分析临时表空间的耗用情况
SQL> select count(*) from big_table; -->开启另一个session
COUNT(*)
----------
2000000
SQL> select * from big_table order by 2,3,4,5,7,8 desc; -->对big_table 实施排序
SQL> alter index pk_stock_tbl_arc rebuild; -->开启另一个session重建索引
SQL> @temp_sort_segment.sql -->可以看到此时temp表空间耗用达到234MB,go_temp的耗用达到375MB
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.0.0 4 234 2 2,07