Oracle临时表空间说明

 

关于oracle临时表空间

一、临时表空间作用

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。 在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。临时表空间的主要作用：
 （1）索引create或rebuild
 （2）Order by 或 group by
 （3）Distinct 操作
 （4）Union 或 intersect 或 minus
 （5）Sort-merge joins
 （6）analyze

排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

 二、临时表空间释放

法一、重启库

库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。

法二、Metalink给出的一个方法

    修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。
SQL>alter tablespace temp increase 1; 
SQL>alter tablespace temp increase 0;

法三、我常用的一个方法

    1、使用如下语句查看一下认谁在用临时段
SELECT username,
       sid,
       serial#,
       sql_address,
       machine,
       program,
       tablespace,
       segtype,
       contents 
  FROM v$session se,
       v$sort_usage su
WHERE se.saddr=su.session_addr  
    2、   那些正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
    3、把TEMP表空间回缩一下
SQL>Alter tablespace TEMP coalesce;

法四、使用诊断事件的一种方法

1、 确定TEMP表空间的ts#
SQL>select ts#, name from sys.ts$ ; 
TS# NAME 
----------------------- 
0 SYSYEM 
1 RBS 
2 USERS 
3* TEMP 
4 TOOLS 
5 INDX 
6 DRSYS 
2、 执行清理操作
SQL>alter session set events 'immediate trace nameDROP_SEGMENTS level 4' ; 
说明： 
temp表空间的TS# 为 3*, So TS#+ 1= 4

法五、　重建TEMP 表空间

Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法来做。　　

准备：查看目前的TemporaryTablespace

　　SQL> select namefrom v$tempfile

1.创建中转临时表空间

create temporarytablespace  TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512MREUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;

　　2.改变缺省临时表空间 为刚刚创建的新临时表空间temp1

　　alter databasedefault  temporary tablespace  temp1;

　　3.删除原来临时表空间

　　drop tablespace tempincluding contents and datafiles;

　　4.重新创建临时表空间

　　create temporarytablespace  TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512MREUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;

　　5.重置缺省临时表空间为新建的temp表空间

　　alter databasedefault  temporary tablespace  temp;

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

　　drop tablespacetemp1 including contents and datafiles;

三、临时表空间增删改

1、改变临时表空间大小

  alter database tempfile'C:\ORADATA\ORCL\TEMP01.DBF' resize 21M;   2、扩展临时表空间

         方法一、增大临时文件大小： 
         SQL> alter databasetempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m; 
         方法二、将临时数据文件设为自动扩展： 
        SQL> alter databasetempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend        on next 5m maxsize unlimited; 
         方法三、向临时表空间中添加数据文件： 
       SQL> alter tablespace tempadd tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

    3、创建临时表空间

  create temporary tablespacetemp01 tempfile 'C:\ORADATA\ORCL\TEMP01.DBF' size 10M;

    4、更改系统的默认临时表空间

  --查询默认临时表空间 
  select * from database_properties whereproperty_name='DEFAULT_TEMP_TABLESPACE'; 
   --修改默认临时表空间 
  alter database default temporary tablespace temp02; 
  --所有用户的默认临时表空间都将切换为新的临时表空间： 
  select username,temporary_tablespace,default_tablespace from dba_users; 
  --更改某一用户的临时表空间： 
  alter user scott temporary tablespace temp02;

   5、删除临时表空间

  --删除临时表空间的一个数据文件： 
  alter database tempfile 'C:\ORADATA\ORCL\TEMP03.DBF' drop; 
  --删除临时表空间(彻底删除)： 
  drop tablespace temp including contents and datafiles cascadeconstraints;

6、临时表空间组介绍

    --1）创建临时表空间组： 
    create temporary tablespace tempts1 tempfile'C:\ORADATA\ORCL\TEMP1_01.DBF' size 2M  tablespace group group1; 
     create temporary tablespace tempts2 tempfile'C:\ORADATA\ORCL\TEMP1_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 temporarytablespace GROUP2; 
    alter database orcl 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

7、对临时表空间进行shrink（11g新增的功能）

    --将temp表空间收缩为20M 
    alter tablespace temp02 shrink space keep 20M; 
    --自动将表空间的临时文件缩小到最小可能的大小 
    ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;

四、临时表空间查询

1、查看临时表空间是否可用

SELECT D.TABLESPACE_NAME,SPACE"SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

SPACE-NVL(FREE_SPACE,0)"USED_SPACE(M)",

 ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",

FREE_SPACE"FREE_SPACE(M)" 

FROM  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,

SUM(BLOCKS) BLOCKS  FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME) D, 

(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE 

FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F 

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

UNION ALL  --if have tempfile  

SELECT D.TABLESPACE_NAME,SPACE"SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 

 USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",

 NVL(FREE_SPACE,0) "FREE_SPACE(M)"

 FROM  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,

SUM(BLOCKS) BLOCKS  FROM DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) D,

 (SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

 ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE 

FROM V$TEMP_SPACE_HEADER  GROUP BY TABLESPACE_NAME) F 

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

2、查看临时表空间的使用情况

（GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询） 
    --GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小 
    --dba_temp_files视图的bytes字段记录的是临时表空间的总大小 
    SELECT temp_used.tablespace_name, 
           total - used as"Free", 
           total as"Total", 
           round(nvl(total -used, 0) * 100 / total, 3) "Free percent" 
      FROM (SELECT tablespace_name, SUM(bytes_used) /1024 / 1024 used 
             FROM GV_$TEMP_SPACE_HEADER 
             GROUPBY tablespace_name) temp_used, 
           (SELECTtablespace_name, SUM(bytes) / 1024 / 1024 total 
             FROM dba_temp_files 
             GROUPBY tablespace_name) temp_total 
     WHERE temp_used.tablespace_name = temp_total.tablespace_name

3、 查找消耗资源比较的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 
  4、查看当前临时表空间使用大小与正在占用临时表空间的sql语句 
  select sess.SID, segtype,blocks * 8 / 1000 "MB", sql_text 
    from v$sort_usage sort, v$session sess, v$sql sql 
   where sort.SESSION_ADDR = sess.SADDR 
     and sql.ADDRESS = sess.SQL_ADDRESS 
   order by blocks desc;

五、临时表空间回收测试

详细测试步骤见附件。