ITPub博客

首页 > 数据库 > Oracle > ORACLE 临时表空间满了的原因解决方案

ORACLE 临时表空间满了的原因解决方案

原创 Oracle 作者:pxbibm 时间:2015-07-28 17:00:13 0 删除 编辑

临时表空间作用 
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。 
重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长。直到耗尽硬盘空间。
数据库temp临时表空间增大,一般在数据安装时设置临时表空间大小,默认的情况下都是设置为自动增长。这样会引起一个问题:在数据库使用中temp表空间会自动扩展的越来越大,造成磁盘空间使用不足。

  引起临时表空间增大主要使用在以下几种情况:

  1、order by or group by (disc sort占主要部分);

  2、索引的创建和重创建;

  3、distinct操作;

  4、union & intersect & minus sort-merge joins;

  5、Analyze 操作;

  6、有些异常也会引起TEMP的暴涨。

  解决temp临时表空间大小问题:

  方法一:直接缩小temp表空间大小

  alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 1024M;

  此语句会直接修改temp表空间的大小,但可能会执行不成功,因为当temp使用率为100%或当前有会话占用时,temp表空间是无法缩小的。

  查询当前会话:

  SELECT se.username,

  se.sid,

  se.serial#,

  se.sql_address,

  se.machine,

  se.program,

  su.tablespace,

  su.SEGTYPE,

  su.CONTENTS

  FROM v$session se,

  v$sort_usage su

  WHERE se.saddr=su.session_addr

  利用此sql语句查询当前会话,然后kill当前会话:

  Alter system kill session 'sid,serial#'

  执行此语句后再对temp表空间resize空间大小就可以了。

  注:此方法只是对temp表空间做临时性的缩小,以后还会继续增大。

  方法二:

  对temp临时表空间重建并设置增长上限值,设置这个值时要预先估算设置合理,不然当增大到此值时会出错,在合理的范围内增加到上限值时数据库会自动释放temp临时表空间。

  重建temp临时表空间:

  1.--启动数据库

  startup

  2.--创建中转临时表空间

  create TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/orcl/TEMP02.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;

  3.--改变缺省临时表空间 为刚刚创建的新临时表空间temp2

  alter database default temporary tablespace temp2;

  4.--删除原来临时表空间

  drop tablespace temp including contents and datafiles;

  5.--重新创建临时表空间

  create TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;

  6.--重置缺省临时表空间为新建的temp表空间

  alter database default temporary tablespace temp;

  7.--删除中转用临时表空间

  drop tablespace temp2 including contents and datafiles;

  8.--重新指定用户表空间为重建的临时表空间

  alter user zxd temporary tablespace temp;

  至此临时表空间增长过大可以更改完成。

  下面是查询在sort排序区使用的执行耗时的SQL:

  Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text

  from v$sort_usage su,v$parameter p,v$session se,v$sql s

  where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr

  order by se.username,se.sid

  此语句可以做跟踪查看分析时用。

扩展临时表空间: 
方法一、增大临时文件大小: 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m; 
方法二、将临时数据文件设为自动扩展: 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited; 
方法三、向临时表空间中添加数据文件: 
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m; 
临时表空间组介绍 
  1)创建临时表空间组: 
create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1; 
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2; 
  
 2)查询临时表空间组:dba_tablespace_groups视图 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP1                         TEMPTS1 
GROUP2                         TEMPTS2 

 3)将表空间从一个临时表空间组移动到另外一个临时表空间组: 
alter tablespace tempts1 tablespace group GROUP2 ; 
select * from dba_tablespace_groups; 

GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS1 
GROUP2                         TEMPTS2 

 4)把临时表空间组指定给用户 
alter user scott temporary tablespace GROUP2; 

 5)在数据库级设置临时表空间 
alter database <db_name> default temporary tablespace GROUP2; 

 6)删除临时表空间组 (删除组成临时表空间组的所有临时表空间) 
drop tablespace tempts1 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS2 

drop tablespace tempts2 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 

对临时表空间进行shrink(11g新增的功能) 
--将temp表空间收缩为20M 
alter tablespace temp shrink space keep 20M; 
--自动将表空间的临时文件缩小到最小可能的大小 
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’; 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12798004/viewspace-1754407/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    240
  • 访问量
    2105234