oracel-temp-临时表空间管理

以下分析适用于11g

v$sort_usage中的SEGTYPE列的不同的值各有什么意义：

    SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数(window function)、建索引等产生的排序。
    DATA：临时表(Global Temporary Table)存储数据使有的段。
    INDEX：临时表上建的索引使用的段。
    HASH：hash算法，如hash连接所使用的临时段。
    LOB_DATA和LOB_INDEX：临时LOB使用的临时段。

根据上述的段类型，大体可以分为三类占用：

    1. SQL语句排序、HASH JOIN占用
    2. 临时表占用
    3. 临时LOB对象占用

    临时表空间的异常占用，一种缓步增长的，另一种情况：一下撑满的通常是一个极大数据量的排序或极大的索引的创建。
    缓步增长的情况，跟系统的内存被逐渐占用类似，存在“泄露”。
    比如排序的SQL游标没有关闭，比如本文的案例；比如会话级临时表产生了数据后一直没有清除；临时LOB对象没有清理或泄露。
    前两种比较好去分析处理，但是临时LOB的泄露问题就复杂很多。

错误观点：

临时表空间中当前占用的空间是由会话当前正在执行的SQL所产生的,事实不一定是当前正在执行的sql产生。
如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。
基于这样的分析，我们只需要随便选择一个占用了比较大临时空间的会话，查询v$open_cursor，查看其打开的游标中是否有大数据量排序的SQL：
SQL> select sql_id,sorts,rows_processed/executions from v$sql  
    where parsing_schema_name='ACCT' and executions>0 and sorts>0  
    and sql_id in (select sql_id from v$open_cursor where sid=4505)  
    order by 3;  
    
select * from v$sql where sql_id='4jqcr3zvxgh3c';    
    

- v$sort_usage的定义中，就明确地说明了SQL_ID列是v$session中的prev_sql_id列，而不是当前的SQL,
- 不过从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL

1.查看使用临时表空间的会话及生成kill session脚本：

SQL_ID_USE_TEMP:真正使用了临时段的SQL
prev_sql_id:session执行过的前一条Sql
CURRENT_SQL_ID：session当前正在执行的Sql
SEGFILE#：临时表空间文件号
SIZE_MB：session当前使用临时表空间大小
status:session状态

select k.inst_id "INST_ID",    
       sid,  
       ktssosno "SERIAL#",  
       username "USERNAME",  
       osuser "OSUSER",   
       program "PROGRAM",
       ktssosqlid "SQL_ID_USE_TEMP",  
       prev_sql_id,
       sql_id "CURRENT_SQL_ID",
       ktssotsn "TABLESPACE",  
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",  
       --注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。  
       decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',   
          5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",  
       ktssofno "SEGFILE#",  
       ktssobno "SEGBLK#",  
       ktssoexts "EXTENTS",  
       ktssoblks "BLOCKS",  
       round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",  
       ktssorfno "SEGRFNO#",
       s.status,
       'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||s.inst_id || ''' immediate;' kill_session_script
from x$ktsso k, gv$session s,   
     (select value from v$parameter where name='db_block_size') p   
where ktssoses = s.saddr  
  and ktssosno = s.serial#
  --and b.tablespace='TEMP'   -- 需要收缩或者删除的临时表空间名称
  --and sid=4128;  

2.temp表空间利用率

SELECT c.tablespace_name,
       to_char(c.bytes / 1024 / 1024 / 1024,
               '99,999.999') total_gb,
       to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024,
               '99,999.999') free_gb,
       to_char(d.bytes_used / 1024 / 1024 / 1024,
               '99,999.999') use_gb,
       to_char(d.bytes_used * 100 / c.bytes,
               '99.99') || '%' USE
  FROM (SELECT tablespace_name,
               SUM(bytes) bytes
          FROM dba_temp_files
         GROUP BY tablespace_name) c,
       (SELECT tablespace_name,
               SUM(bytes_cached) bytes_used
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) d
 WHERE c.tablespace_name = d.tablespace_name;
 

SELECT s.tot_used_blocks,f.total_blocks,(s.tot_used_blocks / f.total_blocks) * 100 AS "percent used"
  FROM (SELECT SUM (used_blocks) tot_used_blocks
          FROM v$sort_segment
         WHERE tablespace_name = 'TEMP1') s,
       (SELECT SUM (blocks) total_blocks
          FROM dba_temp_files
         WHERE tablespace_name = 'TEMP1') f;

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;         
         

3.查看哪些用户哪些进程在使用temp，gv$sort_usage使用情况，关联gV$session，并排序

SELECT t.inst_id,
       t.session_addr,
       s.sid || ',' || s.serial# session_serial,
       t.username,
       t."USER",
       t.sql_id,
       t.tablespace,
       t.contents,
       SUM(t.blocks) temp_blocks,
       SUM(t.blocks * dt.block_size) / 1024 / 1024 AS temp_mb
  FROM gv$sort_usage   t,
       gv$session      s,
       dba_tablespaces dt
 WHERE t.tablespace = dt.tablespace_name
   AND t.session_addr = s.saddr(+)
   AND t.inst_id = s.inst_id(+)
 GROUP BY t.inst_id,
          t.username,
          t."USER",
          t.session_addr,
          s.sid,
          s.serial#,
          t.sql_id,
          t.tablespace,
          t.contents
 ORDER BY temp_blocks DESC; 


SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

查询历史的temp表空间的使用的SQL_ID

select a.SQL_ID,
       a.SAMPLE_TIME,
       a.program,
       sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
  from v$active_session_history a
 where TEMP_SPACE_ALLOCATED is not null 
 and sample_time between
 to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
 group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
 order by 2 asc,4 desc;

4.其他常用管理语句

查看数据库中包含几个临时表空间：

 select * from dba_temp_free_space;

创建临时表空间：

create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/temp2.dbf' size 10G; 

更改系统的默认临时表空间，释放磁盘空间

--查询默认临时表空间

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间

alter database default temporary tablespace temp2;

查看用户默认临时表空间：

select username,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users;

更改某一用户的临时表空间：

alter user scott temporary tablespace temp2; 

给临时表空间添加文件：

alter tablespace temp2 add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 1G autoextend off;

删除临时表空间的一个数据文件:

alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop including datafiles;

删除临时表空间(彻底删除)：

 drop tablespace temp1 including contents and datafiles cascade constraints;

收缩临时表空间：

alter tablespace MESTEMP shrink space keep 500m;

缩小临时表空间大小

alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 100M;

收缩临时表空间某个文件：

alter tablespace MESTEMP shrink tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' keep 1G;

参考：
http://blog.itpub.net/26736162/viewspace-2140629/

v$sort_usage视图定义：

SELECT x$ktsso.inst_id,
       username,
       username,
       ktssoses,
       ktssosno,
       prev_sql_addr,
       prev_hash_value,
       prev_sql_id,
       ktssotsn,
       DECODE (ktssocnt,  0, 'PERMANENT',  1, 'TEMPORARY'),
       DECODE (ktssosegt,
               1, 'SORT',
               2, 'HASH',
               3, 'DATA',
               4, 'INDEX',
               5, 'LOB_DATA',
               6, 'LOB_INDEX',
               'UNDEFINED'),
       ktssofno,
       ktssobno,
       ktssoexts,
       ktssoblks,
       ktssorfno
  FROM x$ktsso, v$session
 WHERE ktssoses = v$session.saddr AND ktssosno = v$session.serial#