临时表空间在8i 时默认还是SYSTEM表空间的一部分，到了9i就要默认的临时表空间，到了10g就出现了临时表空间组，从这个变化过程可以看到Oracle对如何更好发挥数据库优势，减小I/O操作的探索过程。临时表空间多用于内存无法满足需求而需要硬盘的排序等操作，如果排序需要的空间很大临时表空间就很大，有可能几分钟就造成没有可用的磁盘空间，而且操作完成后不可能自动回到初始时的大小，就像房价一样，只涨不跌，所以需要“调控”，需要给它限制最大容量。

实验： 临时表空间的空间变化和调整

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 6 13:53:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn as sysdba

请输入用户名:  system

输入口令: *****

已连接。

通过v$sysstat可看到排序信息：

SQL> desc v$sysstat

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 STATISTIC#                                         NUMBER

 NAME                                               VARCHAR2(64)

 CLASS                                              NUMBER

 VALUE                                              NUMBER

 STAT_ID                                            NUMBER

SQL> select name,value from v$sysstat

     where name like 'sort%';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

sorts (memory)                                                         2967

sorts (disk)                                                              0

sorts (rows)                                                          17623

原先的临时表空间为21M.

SQL>  select name,bytes/1024/1024/1024 from v$tempfile;

NAME

-------------------------------------------------------------------------

BYTES/1024/1024/1024

--------------------

I:\ORADATA\ORC1\TEMP01.DBF

          .020507813

经过下面的一个排序，导致临时表空间迅速增大，到达1.76G,出现空间不足，但排序仍没有进行完。

SQL> select count(*) from dba_tab_columns;

  COUNT(*)

----------

     56078

SQL> set linesize 5000;

SQL> select * from dba_tab_columns a, dba_tab_columns b

  2  order by a.owner,b.table_name;

ERROR:

ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

ORA-27059: 无法减小文件大小

OSD-04005: SetFilePointer() 失败, 无法读取文件

O/S-Error: (OS 112) 磁盘空间不足。

正常后，临时表空间还是不会变小，仍然是1.76G.所以只能修改到原来的值：

SQL> alter database tempfile 'I:\ORADATA\ORC1\TEMP01.DBF'  resize 21m;

数据库已更改。

SQL>  select name,bytes/1024/1024/1024 from v$tempfile;

NAME

-------------------------------------------------------------------------

BYTES/1024/1024/1024

--------------------

I:\ORADATA\ORC1\TEMP01.DBF

          .020507813

后来把上面的查询改到查询1000行，还是显示空间不足：

SQL> select * from dba_tab_columns a, dba_tab_columns b

  2  where rownum < 1000 order by a.owner,b.table_name;

ERROR:

ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

ORA-27059: 无法减小文件大小

OSD-04005: SetFilePointer() 失败, 无法读取文件

O/S-Error: (OS 112) 磁盘空间不足。

修改到原来的值：

SQL> alter database tempfile 'I:\ORADATA\ORC1\TEMP01.DBF'  resize 21m;

数据库已更改。

把上面的查询改到查询100行：

SQL> select a.owner,a.table_name,a.column_name from dba_tab_columns a, dba_tab_columns b

2         where rownum < 100 order by a.owner,b.table_name;

此时TEMP大小为76M.

看来一个排序也能影响到硬盘空间的使用，实验后我马上看了现场数据库的临时表空间大小，还好，只有72M,数据量不大。

设定最大值，防止导致空间不足问题：

SQL> alter database tempfile 'I:\ORADATA\ORC1\TEMP01.DBF' autoextend on next 1024k

  2  maxsize 70m;

数据库已更改。

SQL> select a.owner,a.table_name,a.column_name from dba_tab_columns a, dba_tab_columns b

  2  where rownum < 100 order by a.owner,b.table_name;

select a.owner,a.table_name,a.column_name from dba_tab_columns a, dba_tab_columns b

                                               *

第 1 行出现错误:

ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

SQL> select name ,bytes/1024/1024 from v$tempfile;

NAME

--------------------------------------------------------------------------------

BYTES/1024/1024

---------------

I:\ORADATA\ORC1\TEMP01.DBF

             70

修改到原来的值：

SQL> alter database tempfile 'I:\ORADATA\ORC1\TEMP01.DBF'  resize 21m;