當排序操作重建索引等大型操作無法在內存中完成時臨時表空間將為排序提供便利一般情況下臨時表空間為多個用戶多個會話所共享不能為會話分批空間配額臨時表空間耗用過度且在不能自動擴展的情形下將收到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的值是否設定合理
From:http://tw.wingwit.com/Article/program/Oracle/201311/17414.html
