oracle 临时表空间-再续

   oracle 临时表空间-再续

 

     今天是2013-11-18，前段时间我同事数据库出现了一次临时表空间不足问题，今天对临时表空间在复习一下，记录一下笔记。

之前写过临时表空间的日志：

http://blog.csdn.net/xiaohai20102010/article/details/10431903

 第一：shrink 临时表空间：

当我们执行一个很大的排序操作语句的时候，如果内存排序空间不足，那么就会占用临时表空间以满足需求，但是当执行完毕后，临时表空间不会自动的是否空间，而是将占用的空间变为free可以available reuse。但是我可以使用shrink space将临时表空间进行空间释放，或是使用shrink tempfile对临时文件大小进行resize。

eg：

SQL> alter tablespace temp shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                   7331840         5234688    3145728

SQL>

可以看出在线shrink的过程中会根据使用的最低占用空间大小进行分配，操作过程不影响正常使用，可以使用keep字段对表空间指定最小大小。

如：

alter tablespace temp shrink space keep 100M;

对tempfile进行shrink操作如下：

SQL> alter tablespace temp shrink tempfile '/opt/app/oracle/oradata/RHYS/temp01.dbf'; 

Tablespace altered.

SQL>  alter tablespace temp shrink tempfile '/opt/app/oracle/oradata/RHYS/temp01.dbf' keep 20M;

Tablespace altered.

SQL>

第二：查看临时表空间内容

在此总结一下11g临时表空间常用视图：

dba_temp_files(查看临时表空间文件信息）

v$temp_files(动态查看临时表空间文件信息）

dba_temp_space_free(查看临时表空间空闲大小）

v$sort_segment(查看排序段信息）

v$tempseg_usage(查看临时段的使用信息）

eg：

 

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                   7331840         5234688    5242880

SQL>

查看哪些用户占用了临时表空间：

  1* select username,user,session_addr,session_num,sqladdr,sql_id ,segtype,contents from v$tempseg_usage

USERNAME                       USER       SESSION_ADDR     SESSION_NUM SQLADDR          SQL_ID        SEGTYPE   CONTENTS
------------------------------ ---------- ---------------- ----------- ---------------- ------------- --------- ---------
SYS                            SYS        000000007B4836E0           5 000000007BC35AF0 0fr8zhn4ymu3v DATA      TEMPORARY

SQL>

查看哪些用户占用临时段，并查处执行的sql信息是什么：

col sql_text for a70
col username for a20
col program for a30
col osuser for a10
col allo_M for 99999999
select distinct(s.sid),s.serial#,s.username,s.osuser,s.program,t.segfile#,t.segtype,t.segblk#,(t.blocks*8082/1024/1024) allo_M,x.sql_text from v$session s,v$sql x,v$tempseg_usage t
where s.saddr=t.session_addr and t.sql_id=x.sql_id and x.sql_text not like 'select distinct(s.sid),s.serial#%';

查看排序操作使用情况参考v$sort_segment视图。

 

That‘s all！