Chinaunix首页 | 论坛 | 博客
  • 博客访问: 84914
  • 博文数量: 93
  • 博客积分: 2141
  • 博客等级: 大尉
  • 技术积分: 785
  • 用 户 组: 普通用户
  • 注册时间: 2011-05-13 14:08
文章分类
文章存档

2011年(93)

我的朋友

分类: Oracle

2011-11-16 10:12:27

数据库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

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

阅读(431) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~