开发者社区> 老牛的博客> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

ORACLE 临时表空间使用率过高的原因及临时解决方案

简介:           数据库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 'd:powerdmsdb\dmsdb\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 'D:\PowerDmsDB\DMSDB\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 'D:\PowerDmsDB\DMSDB\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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle常见问题及解决方案
  没有人会否认Oracle是全球最有影响的数据库产品之一;但是庞大的系统,总是会出现各种各样的问题,很多经验不足的使用者面对着那些错误提示束手无策。本文是整理了Oracle数据库在使用过程中一些常见的问题及解决方案,希望能给初学者一点启示。
856 0
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1890 0
在 Linux x86 上安装 Oracle 数据库 10g_2
如果您按照以上说明安装了 Linux,且内核参数全部采用默认值,则只需在以 root 用户身份登录后执行下命令。 cat >> /etc/sysctl.conf /etc/sysctl.conf kernel.
833 0
在 Linux x86 上安装 Oracle 数据库 10g_4
自动存储管理 (ASM) 我把最重要的内容留在了最后。ASM 是 Oracle 数据库 10g 中一个非常出色的新特性,它以平台无关的方式提供了文件系统、逻辑卷管理器以及软件 RAID 等服务。ASM 可以条带化和镜像磁盘,从而实现了在数据库被加载的情况下添加或移除磁盘以及自动平衡 I/O 以删除“热点”。
918 0
+关注
老牛的博客
喜欢研究数据库维护,互联网运营的朋友,可以加我QQ:52234858
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PolarDB的Oracle平滑迁移之路
立即下载
迁移 ORACLE 最佳实践
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载