临时表空间历险[转]

1、环境：
OS:WINDOWS XP
数据库：Oracle 9.2.0.1.0

2、起因
今天在看《oracle性能优化技术内幕》，中间有一个关于数据库缓冲区高速缓存的脚本：

Select o.owner,o.object_type,o.object_name,
Count(b.OBJD) From v$bh b,dba_objects o
Group By o.owner ,o.object_type,o.object_name
Having Count(b.OBJD)>(Select
to_number(Value*0.5) From v$parameter
Where Name = 'db_block_buffers')

用sys用户登陆数据库执行该脚本，然后一直处于正在执行的状态，然后报错，
“无法通过128（在表空间temp中）扩展 temp 段”，确定之后，检查临时表空间，是因为临时表空间满了，而且没有设置自动扩展，设置自动扩展之后，开始重新查询，我的temp表空间参数：大小200m，自动扩展，每次扩展100m。在执行该脚本的同时，用oem观察表空间的大小，一直到表空间增长至4g左右的时候，查询依然没有结果。此时表空间已经很大了，因此在考虑临时表空间到底是如何增长的呢？在什么情况下释放空间？是否可以收缩？

3、为此做如下测试：

1) 首先重建temp表空间，并重启数据库，确保temp表空间没有被占用
C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 7 16:14:09 2007

Copyright (c) 1982, 2002, oracle Corporation.  All rights reserved.


连接到:
oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
orACLE 例程已经关闭。
SQL> startup;
orACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL>

2) 检查数据库当前大小和已使用情况
当前大小200M，未使用，


3) 执行下面的脚本，持续50秒
Select o.owner,o.object_type,o.object_name,
Count(b.OBJD) From v$bh b,dba_objects o
Group By o.owner ,o.object_type,o.object_name
Having Count(b.OBJD)>(Select
to_number(Value*0.5) From v$parameter
Where Name = 'db_block_buffers')

50秒之后该脚本依然没有执行出结果，中止该脚本的执行，当前数据库临时表空间大小900M，占用894M。

4、临时表空间的作用

oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要对查询的结果进行排序
1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。

关于临时表空间的释放，在网上查了资料，确认重启数据库可释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。在网上看到有个帖子提供了几种释放的方法，但是有的不能应用于当前的版本。此外没有发现好的释放临时表空间的方法。


5、临时表空间的释放测试

使用语句进行表空间大小的修改：
SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2      200M;
Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
*
ERROR 位于第 1 行:
orA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

关闭并重启数据库，查看临时表空间的使用情况，当前大小900M，已使用0M

查资料显示oracle系统smon进程会自动清除的，个人并不明确smon什么时候清除临时段。

做如下测试：

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2      1500M;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2      900M;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2      200M;
Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
*
ERROR 位于第 1 行:
orA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

证明temp表空间被限制在了900M，新建表空间：

SQL> Create
  2      TEMPORARY TABLESPACE "TEMP2" TEMPFILE
  3      'D:\ORACLE\ORADATA\ZERO\TEMP2.ora' SIZE 200M EXTENT
  4      MANAGEMENT LOCAL UNIFORM SIZE 1M;

表空间已创建。

SQL> Alter DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

数据库已更改。


然后执行sql语句

SQL> Select * From test.sal_salary ss order By ss.jsdjgz;

该语句结果较多，就不列出了，查看新临时表空间的使用情况：

大小200M，已用67M，执行下面的语句：


SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE
  2       100M;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。
SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
orA-03297: 文件包含在请求的 RESIZE 值以外使用的数据


进行如下操作：

SQL> shtudown immediate;
SP2-0734: 未知的命令开头 "shtudown i..." - 忽略了剩余的行。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
orACLE 例程已经关闭。
SQL> startup;
orACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m;
Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
orA-03297: 文件包含在请求的 RESIZE 值以外使用的数据


SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 200m;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。

SQL> Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m;
Alter DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
orA-03297: 文件包含在请求的 RESIZE 值以外使用的数据


SQL>


切换回原有的temp表空间，并重启数据库：

SQL> Alter DATABASE DEFAULT
  2      TEMPORARY TABLESPACE "TEMP";

数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
orACLE 例程已经关闭。
SQL> startup;
orACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL>
SQL> drop  TABLESPACE temp2 including contents and datafiles;

表空间已丢弃。

目前从表象看temp表空间的已使用为0，但是执行如下脚本：

SQL> Select * From test.sal_salary ss order By ss.jsdjgz;
执行完之后，temp表空间的已用变为894M。

结果证明在重启数据库之后其实oracle并没有释放temp表空间被占用的部分，虽然从表象看是已经释放了，但是一旦再次有问题排序出现，马上temp表空间的大小就增大至上次释放之前的大小。不知道这个到底是Oracle该版本的bug还是Oracle就是这么处理temp表空间的。

6、重建表空间：
C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 7 18:23:03 2007

Copyright (c) 1982, 2002, oracle Corporation.  All rights reserved.


连接到:
oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> Create
  2      TEMPORARY TABLESPACE "TEMP2" TEMPFILE
  3      'D:\ORACLE\ORADATA\ZERO\TEMP2.ora' SIZE 200M EXTENT
  4      MANAGEMENT LOCAL UNIFORM SIZE 1M;

表空间已创建。

SQL> Alter DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

数据库已更改。
SQL> drop tablespace temp including contents and datafiles;

表空间已丢弃。

SQL> Create
  2      TEMPORARY TABLESPACE "TEMP" TEMPFILE
  3      'D:\ORACLE\ORADATA\ZERO\TEMP1.ora' SIZE 200M REUSE AUTOEXTEND
  4      ON NEXT  100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
  5      UNIFORM SIZE 1024K;

表空间已创建。

SQL> Alter DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

数据库已更改。

SQL> drop tablespace temp2 including contents and datafiles;

表空间已丢弃。

SQL>
至此关于temp表空间的历险结束了，虽然最终没有达到我所想的手动释放表空间的效果，不过还是有收获的，发贴以供大家讨论，谢谢！

文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: OracleOracle
相关日志: