收缩临时表空间

版权声明：本文为博主原创文章，欢迎扩散，扩散请务必注明出处。

        当排序操作、重建索引等大型操作无法在内存中完成时，临时表空间将为排序提供便利。一般情况下临时表空间为多个用户，多个会话所共
享。不能为会话分批空间配额。临时表空间耗用过度且在不能自动扩展的情形下将收到“ORA-1652:unable to extend temp segment” 错误.下面
描述了过度扩展后如何释放临时表空间。

与之相关的内容参考：
        Oracle 表空间与数据文件
        临时表空间的管理与受损恢复
        Oracle 彻底 kill session

一、临时表空间何时释放
        检索数据的会话游标关闭时，占用的临时空间即被释放
        数据库关闭，重启(一般情况),会话 log off

二、释放过大的临时表空间
[sql] view plain copy print?
  1. <span style="font-family:Verdana;">  
  2. 1、查看当前临时表空间的情况  
  3. SQL> select * from v$version where rownum<2;  
  4.   
  5. BANNER  
  6. ----------------------------------------------------------------  
  7. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
  8. SQL> @temp_sort_segment   
  9.   
  10. +==================================================================================+  
  11. | Segment Name            : The segment name is a concatenation of the             |  
  12. |                           SEGMENT_FILE (File number of the first extent)         |  
  13. |                           and the                                                |  
  14. |                           SEGMENT_BLOCK (Block number of the first extent)       |  
  15. | Current Users           : Number of active users of the segment                  |  
  16. | Total Temp Segment Size : Total size of the temporary segment in MB              |  
  17. | Currently Used Bytes    : Bytes allocated to active sorts                        |  
  18. | Extent Hits             : Number of times an unused extent was found in the pool |  
  19. | Max Size                : Maximum number of MB ever used                         |  
  20. | Max Used Size           : Maximum number of MB used by all sorts                 |  
  21. | Max Sort Size           : Maximum number of MB used by an individual sort        |  
  22. | Free Requests           : Number of requests to deallocate                       |  
  23. +==================================================================================+  
  24. -->此时临时表空间go_temp中达到了32GB  
  25.     Tablespace  Segment Current Currently Pct.   Extent      Max Max Used Max Sort     Free  
  26.           Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests  
  27. -------------- -------- ------- --------- ---- -------- -------- -------- -------- --------  
  28. TEMP           SYS.0.0        4         4    2    1,864      217      217      217        0  
  29. GO_TEMP        SYS.0.0        0         0    0    1,305   32,766      367      367        0  
  30. **************          ------- ---------      -------- -------- -------- -------- --------  
  31. sum                           4         4         3,169   32,983      584      584        0  
  32.   
  33. SQL> col tbsname format a15  
  34. SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status   
  35.   2  from v$tablespace s,v$tempfile t                               
  36.   3  where s.ts# = t.ts#;                                           
  37.   
  38. TBSNAME         NAME                                                       MB STATUS  
  39. --------------- -------------------------------------------------- ---------- -------  
  40. TEMP            /u02/database/ORADB/temp/tempORADB.dbf                  235 ONLINE  
  41. GO_TEMP         /u02/database/ORADB/temp/ORADB_tempORADB.dbf          32767 ONLINE  
  42.   
  43. SQL> @temp_usage2      -->此时temp已使用的为4MB，而GO_TEMP未使用  
  44.   
  45. TABLESPACE             MB_TOTAL    MB_USED    MB_FREE  
  46. -------------------- ---------- ---------- ----------  
  47. GO_TEMP                 32767          0      32767  
  48. TEMP                      218          4        214  
  49.   
  50. 2、观察及分析临时表空间的耗用情况  
  51. SQL> select count(*) from big_table;    -->开启另一个session  
  52.   
  53.   COUNT(*)  
  54. ----------  
  55.    2000000  
  56.   
  57. SQL> select * from big_table order by 2,3,4,5,7,8 desc;  -->对big_table 实施排序  
  58.   
  59. SQL> alter index pk_stock_tbl_arc rebuild;      -->开启另一个session重建索引  
  60.   
  61. SQL> @temp_sort_segment.sql           -->可以看到此时temp表空间耗用达到234MB,go_temp的耗用达到375MB  
  62.   
  63.     Tablespace  Segment Current Currently Pct.   Extent      Max Max Used Max Sort     Free  
  64.           Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests  
  65. -------------- -------- ------- --------- ---- -------- -------- -------- -------- --------  
  66. TEMP           SYS.0.0        4       234    2    2,077      234      234      230        0  
  67. GO_TEMP        SYS.0.0        1       375    1    2,055   32,766      375      375        0  
  68. **************          ------- ---------      -------- -------- -------- -------- --------  
  69. sum                           5       609         4,132   33,000      609      605        0  
  70.   
  71. SQL> @temp_sort_users.sql  -->获得当前排序的会话  
  72.   
  73.    INST_ID SID_SERIAL Username   OSUSER          SPID         MODULE     PROGRAM       MB_USED TABLESPACE STATEMENTS  
  74. ---------- ---------- ---------- --------------- ------------ ---------- ---------- ---------- ---------- ----------  
  75.          1 1064,9259  SCOTT      oracle          14456        SQL*Plus   oracle@SZD        234 TEMP                4  
  76.                                                                          B (TNS V1-  
  77.                                                                          V3)  
  78.   
  79.          1 1073,5166  GO_ADMIN   oracle          2480         SQL*Plus   oracle@SZD        375 GO_TEMP           1  
  80.                                                                          B (TNS V1-  
  81.                                                                          V3)  
  82. 3、使用resize，缩小临时表空间，如不能缩小，转到下一步  
  83. SQL> SELECT 'alter database tempfile ''' || a.name || ''' resize ' || b.siz || 'M;'  resize_command  
  84.   2  FROM v$tempfile a  
  85.   3        ,(SELECT ceil(tmsize.maxblk * bk.value / 1024 / 1024) siz  
  86.   4       FROM (SELECT nvl(MAX(segblk#), 128) maxblk  
  87.   5            FROM v$sort_usage) tmsize  
  88.   6             ,(SELECT VALUE  
  89.   7            FROM v$parameter  
  90.   8            WHERE NAME = 'db_block_size') bk) b;  
  91.   
  92. RESIZE_COMMAND  
  93. ----------------------------------------------------------------------------------------  
  94. alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;  
  95. alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;  
  96.   
  97. -->实际上此时占用32GB的临时数据文件已经缩小  
  98. alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;  
  99.   
  100. Database altered.  
  101.   
  102. -->为便于演示，此时假定TEMP为过大的临时表空间且不能释放  
  103. -->下面调整表明已使用空间超出了分配的空间  
  104. SQL> alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;    
  105. alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M  
  106. *  
  107. ERROR at line 1:  
  108. ORA-03297: file contains used data beyond requested RESIZE value  
  109.   
  110. SQL> select count(*) from v$sort_usage where tablespace='TEMP';   -->当前有未释放的临时段  
  111.   
  112.   COUNT(*)  
  113. ----------  
  114.          4  
  115.            
  116. /**************************************************/  
  117. /* Author: Robinson Cheng                         */  
  118. /* Blog:   http://blog.csdn.net/robinson_0612     */  
  119. /* MSN:    robinson_0612@hotmail.com              */  
  120. /* QQ:     645746311                              */  
  121. /**************************************************/      
  122.        
  123. 4、新建一个中转临时表空间  
  124. SQL> create temporary tablespace temp2 tempfile '/u02/database/ORADB/temp/ORADB_temp02.dbf'   
  125.   2  size 10m autoextend on;  
  126.     
  127. Tablespace created.  
  128.   
  129. -->如果此时过大的临时表空间为缺省的临时表空间，则必须将缺省的临时表空间设置为新的临时表空间之后  
  130. SQL> select property_name,property_value from database_properties  
  131.   2  where property_name like 'DEFAULT_TEMP_TABLESPACE';  
  132.   
  133. PROPERTY_NAME                  PROPERTY_VALUE  
  134. ------------------------------ --------------------  
  135. DEFAULT_TEMP_TABLESPACE        TEMP  
  136.     
  137. SQL> alter database default temporary tablespace temp2;  
  138.   
  139. Database altered.  
  140.   
  141. 5、转移用户到中转临时表空间  
  142. -->过大临时表空间上的那些用户需要迁移到新建的临时表空间  
  143. -->查询dba_users视图查询哪些用户位于过大的临时表空间之上  
  144. -->并使用下面的命令将其切换到新的临时表空间  
  145. alter user <username> temporary tablespace temp2;   
  146.   
  147. 6.等到过大临时表空间上的没有临时段被使用，即已经全部释放即可删除过大的临时表空间  
  148.   
  149. SQL> show user;    -->由于当前用户为scott，所以临时表空间未能释放  
  150. USER is "SCOTT"  
  151.   
  152. SQL> conn / as sysdba   -->切换到sysdba  
  153. Connected.  
  154.   
  155. SQL> @temp_usage2      -->临时段已经被释放  
  156.   
  157. TABLESPACE             MB_TOTAL    MB_USED    MB_FREE  
  158. -------------------- ---------- ---------- ----------  
  159. GO_TEMP                   106          0        106  
  160. TEMP                      235          0        235  
  161.   
  162. -->如果没有释放在可以kill session的情况下kill session.利用前面获得的sid,serial#来执行(前提是允许该情况发生).  
  163. alter system kill session '1064,9259'  
  164.   
  165. 7.删除过大的临时表空间  
  166.   
  167. SQL> alter tablespace temp tempfile offline;   -->先将其脱机  
  168.   
  169. Tablespace altered.  
  170.   
  171. SQL> drop tablespace temp including contents and datafiles;   -->删除临时表空间及相应的文件  
  172.   
  173. Tablespace dropped.  
  174.   
  175. SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status                      
  176.   2  from v$tablespace s,v$tempfile t                                                  
  177.   3  where s.ts# = t.ts#;                                                              
  178.                                                                                        
  179. TBSNAME         NAME                                                       MB STATUS   
  180. --------------- -------------------------------------------------- ---------- -------  
  181. GO_TEMP       /u02/database/ORADB/temp/ORADB_tempORADB.dbf           106 ONLINE   
  182. TEMP2        /u02/database/ORADB/temp/ORADB_temp02.dbf                10 ONLINE   
  183.   
  184. -->也可以使用下面的命令来完成仅仅删除单个文件  
  185. ALTER DATABASE TEMPFILE '/u02/database/ORADB/temp/tempORADB.dbf' DROP INCLUDING DATAFILES; -->删除单个文件  
  186.   
  187. 7、根据需求可以创建原来的临时表空间并将切换出去用户切换到此临时表空间</span>  

三、总结
1、关注alert_<sid>.log文件中的ORA-1652错误并调查什么原因导致该错误。有些时候并不是由于当前的SQL 导致临时表空间不能扩展，很可能
        由于前一个SQL耗用了99%的临时表空间，而后一个SQL执行时即出现错误。对于此类情况应调查前一SQL并调整避免过多的磁盘排序。
2、如果基于空间压力应该关闭临时表空间的自动扩展。因此为临时表空间设定合理的大小就成了一个问题。个人的解决方案是首先检查ORA-1652
    其次是观察业务高峰期的峰值。如前面查询中的字段Max Size(: Maximum number of MB ever used)的值来预估。如果大师们有更好的建议
    不妨拍砖。
3、通过重启数据库，临时表空间所耗用的大小有时候并不能缩小。
4、在Oracle 11g之前一般是通过创建中转临时表空间来达到缩小的目的。不是很完美，因为有些时候临时段未释放导致不能删除临时表空间及
        数据文件。在11g可以直接使用下面的命令来完成：
        alter tablespace temp shrink space;
        alter tablespace temp shrink tempfile '<dir>' keep n <mb/kb>;
5、系统缺省的临时表空间不能被删除，因此如果系统缺省的临时表空间过大删除前应该新置一个系统缺省的临时表空间。
6、删除过大临时表空间前其上的用户应该先将其设定到中转临时表空间，重建后再将其置回原状态。
7、减少磁盘排序的首要任务调整SQL，如避免笛卡尔积，为表添加合理的索引等。其次要考虑PGA的值是否设定合理。

四、相关参考
        Oracle 表空间与数据文件
        临时表空间的管理与受损恢复
        Oracle 彻底 kill session
 
posted @ 2017-07-20 10:10  seasonzone  阅读(220)  评论(0)  编辑  收藏  举报