当前位置:  数据库>oracle

Oracle收缩临时表空间

    来源: 互联网  发布时间:2017-05-23

    本文导语: 当排序操作、重建索引等大型操作无法在内存中完成时,临时表空间将为排序提供便利。一般情况下临时表空间为多个用户,多个会话所共享。不能为会话分批空间配额。临时表空间耗用过度且在不能自动扩展的情形下将收到“...

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

与之相关的内容参考:

Oracle 表空间与数据文件

Oracle 临时表空间的管理与受损恢复

Oracle 彻底 kill session

一、临时表空间何时释放

检索数据的会话游标关闭时,占用的临时空间即被释放

数据库关闭,重启(一般情况),会话 log off

二、释放过大的临时表空间


1、查看当前临时表空间的情况
SQL> select * from v$version where rownum @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,077      234      234      230        0
GO_TEMP        SYS.0.0        1      375    1    2,055  32,766      375      375        0
**************          ------- ---------      -------- -------- -------- -------- --------
sum                          5      609        4,132  33,000      609      605        0

SQL> @temp_sort_users.sql  -->获得当前排序的会话

  INST_ID SID_SERIAL Username  OSUSER          SPID        MODULE    PROGRAM      MB_USED TABLESPACE STATEMENTS
---------- ---------- ---------- --------------- ------------ ---------- ---------- ---------- ---------- ----------
        1 1064,9259  SCOTT      oracle          14456        SQL*Plus  oracle@SZD        234 TEMP                4
                                                                        B (TNS V1-
                                                                        V3)

        1 1073,5166  GO_ADMIN  oracle          2480        SQL*Plus  oracle@SZD        375 GO_TEMP          1
                                                                        B (TNS V1-
                                                                        V3)
3、使用resize,缩小临时表空间,如不能缩小,转到下一步
SQL> SELECT 'alter database tempfile ''' || a.name || ''' resize ' || b.siz || 'M;'  resize_command
  2  FROM v$tempfile a
  3        ,(SELECT ceil(tmsize.maxblk * bk.value / 1024 / 1024) siz
  4      FROM (SELECT nvl(MAX(segblk#), 128) maxblk
  5            FROM v$sort_usage) tmsize
  6            ,(SELECT VALUE
  7            FROM v$parameter
  8            WHERE NAME = 'db_block_size') bk) b;

RESIZE_COMMAND
----------------------------------------------------------------------------------------
alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;
alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;

-->实际上此���占用32GB的临时数据文件已经缩小
alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;

Database altered.

-->为便于演示,此时假定TEMP为过大的临时表空间且不能释放
-->下面调整表明已使用空间超出了分配的空间
SQL> alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M; 
alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> select count(*) from v$sort_usage where tablespace='TEMP';  -->当前有未释放的临时段

  COUNT(*)
----------
        4
       
/**************************************************/
/* Author: Robinson Cheng                        */
/* Blog:  http://blog.csdn.net/robinson_0612    */
/* MSN:    robinson_0612@hotmail.com              */
/* QQ:    645746311                              */
/**************************************************/   
   
4、新建一个中转临时表空间
SQL> create temporary tablespace temp2 tempfile '/u02/database/ORADB/temp/ORADB_temp02.dbf'
  2  size 10m autoextend on;
 
Tablespace created.

-->如果此时过大的临时表空间为缺省的临时表空间,则必须将缺省的临时表空间设置为新的临时表空间之后
SQL> select property_name,property_value from database_properties
  2  where property_name like 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        TEMP
 
SQL> alter database default temporary tablespace temp2;

Database altered.

5、转移用户到中转临时表空间
-->过大临时表空间上的那些用户需要迁移到新建的临时表空间
-->查询dba_users视图查询哪些用户位于过大的临时表空间之上
-->并使用下面的命令将其切换到新的临时表空间
alter user temporary tablespace temp2;

6.等到过大临时表空间上的没有临时段被使用,即已经全部释放即可删除过大的临时表空间

SQL> show user;    -->由于当前用户为scott,所以临时表空间未能释放
USER is "SCOTT"

SQL> conn / as sysdba  -->切换到sysdba
Connected.

SQL> @temp_usage2      -->临时段已经被释放

TABLESPACE            MB_TOTAL    MB_USED    MB_FREE
-------------------- ---------- ---------- ----------
GO_TEMP                  106          0        106
TEMP                      235          0        235

-->如果没有释放在可以kill session的情况下kill session.利用前面获得的sid,serial#来执行(前提是允许该情况发生).
alter system kill session '1064,9259'

7.删除过大的临时表空间

SQL> alter tablespace temp tempfile offline;  -->先将其脱机

Tablespace altered.

SQL> drop tablespace temp including contents and datafiles;  -->删除临时表空间及相应的文件

Tablespace dropped.

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
--------------- -------------------------------------------------- ---------- -------
GO_TEMP      /u02/database/ORADB/temp/ORADB_tempORADB.dbf          106 ONLINE
TEMP2        /u02/database/ORADB/temp/ORADB_temp02.dbf                10 ONLINE

-->也可以使用下面的命令来完成仅仅删除单个文件
ALTER DATABASE TEMPFILE '/u02/database/ORADB/temp/tempORADB.dbf' DROP INCLUDING DATAFILES; -->删除单个文件

7、根据需求可以创建原来的临时表空间并将切换出去用户切换到此临时表空间

三、总结

1、关注alert_.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 '' keep n ;

5、系统缺省的临时表空间不能被删除,因此如果系统缺省的临时表空间过大删除前应该新置一个系统缺省的临时表空间。

6、删除过大临时表空间前其上的用户应该先将其设定到中转临时表空间,重建后再将其置回原状态。

7、减少磁盘排序的首要任务调整SQL,如避免笛卡尔积,为表添加合理的索引等。其次要考虑PGA的值是否设定合理。

 


    
 
 

您可能感兴趣的文章:

  • oracle查看表空间已分配和未分配空间的语句分享
  • shell脚本操作oracle删除表空间、创建表空间、删除用户
  • oracle 创建表空间步骤代码
  • oracle增加表空间大小两种实现方法
  • RedHat8上解压Oracle9磁盘丢失磁盘空间,请大虾帮忙!
  • ORACLE数据库空间整理心得
  • oracle9i 在 redhat7.3 上安装临时目录空间不够.
  • Oracle 10g创建表空间和用户并指定权限
  • Linux下Oracle传输表空间高手支招
  • Oracle释放undo表空间
  • oracle 创建表空间详细介绍
  • 在linux下装oracle9i到了最后时候,提示/tmp空间不够了,怎么增加,谢谢!
  • 查看修改Oracle10G归档日志空间的限制
  • [小技巧]手工扩大Oracle数据库表空间的方法
  • Oracle回滚段空间回收步骤
  • Oracle数据库手工扩大表空间的方法
  • Oracle tablespace表空间调整
  • 新手求助:RedHat安装oracle 11g 检测空间不足
  • Oracle安装过程中物理内存检查及临时temp空间不足问题解决
  • Oracle 10g表空间创建的完整步骤
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,