Temp临时表空间处理方法

         临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理。临时表空间消耗的主要原因是需要对查询的中间结果进行排序，当oracle里需要用到sort的时候,PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序,同时如果有异常情况的话,也会被放入临时表空间,正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临时段的。但有时会遇到临时段没有被释放,TEMP表空间持续增长到耗尽磁盘空间。当数据库实例重启后，临时表空间一般就会释放掉。

        需要注意的是，当前临时表空间文件的大小是历史上使用临时表空间最大的大小，并不代表现在实际使用的大小。

        当临时表空间撑满后，就会影响到系统性能，这时我们就需要人为干涉来适当释放表空间大小。

        具体处理方法归结起来有如下几种：

        一、shrink收缩表空间（11g新增功能，支持在线收缩）

        将temp表空间收缩为20M

alter tablespace temp shrink space keep 20M; 

        自动将表空间的临时文件缩小到最小可能的大小

alter tablespace temp shrink  tempfile  '/u01/app/oracle/data/temp02.dbf'

        或者直接shink

alter tablespace temp shrink;

        二、temp临时表空间添加数据文件

alter tablespace "TEMP" add tempfile '+data/ora/tempfile/temp01.dbf' size 800m;

        三、重建临时表空间

        由于oracle默认的临时表空间是不能直接drop的，所以我们需要通过创建中转临时表空间的方法来实现间接删除重建临时表空间的目的。

        首先查看现有temp临时表的名字及路径

SQL> select name from v$tempfile;

/u01/app/oracle/oradata/tempfile/temp.dbf

        查看哪些用户使用临时表空间

SQL> select username,temporary_tablespace from dba_users;

USERNAME  TEMPORARY_TABLESPACE

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

PILIANG  TEMP

TEST  TEMP

ORACLE_OCM        TEMP

        1.创建中转表空间

create temporary tablespace  temp01 tempfile '/u01/app/oracle/oradata/tempfile/temp01.dbf' size 512M reuse autoextend on next  1M maxsize 2G;(临时表空间不建议设置成无限增长大小，建议设置一个固定最大值) 

        2.改变默认临时表空间为temp01

alter database default  temporary tablespace  temp01;

        3.确认默认表空间是否为新创建的temp01

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

        4.查询出正在使用temp表空间的会话进程并删除掉。

select se.username,se.sid,se.serial#,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 system kill session 'sid,serial#';

        5.删除原来临时表空间

drop tablespace temp including contents and datafiles;

        6.重新创建temp临时表空间

create temporary tablespace  temp tempfile '/u01/app/oracle/oradata/tempfile/temp.dbf' size 512M reuse autoextend on next 100M maxsize 3g;

        7.重置默认临时表空间为temp

alter database default  temporary tablespace  temp;

        8.删除中转用临时表空间

drop tablespace temp01 including contents and datafiles;

        9.如果有必要，重新指定用户表空间为重建的临时表空间

alter user test temporary tablespace  temp; 

        四、重启数据库

        五、优化占用temp最大的SQL语句。

        其实造成temp表空间增大的最主要原因还是SQL语句的不规范造成的排序中间结果集过多过大撑满了temp表空间。所以，避免temp表空间增大的最优解决办法还是优化SQL。shrink的应用也大大方便了回缩空间的任务。