临时表空间作用
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’;

鸾舞春秋

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

公告