DBA命令速查6： 临时表空间（ Temporary Tablespace）的相关确认SQL

select * from dba_temp_free_space;

SELECT d.tablespace_name "Name" ,
       NVL(a.bytes / 1024 / 1024, 0) "Size(MB)",
       NVL(t.bytes, 0) / 1024 / 1024 "Used(MB)",
       NVL(t.bytes / a.bytes * 100, 0) "Used(%)",
       NVL(a.bytes - NVL(t.bytes, 0), 0)/1024/1024 "Free(MB)",
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
FROM sys.dba_tablespaces d,
    (select tablespace_name,
            sum(bytes) bytes,
            count(file_id) count
     from dba_temp_files
     group by tablespace_name) a,
    (select ss.tablespace_name ,
            sum((ss.used_blocks*ts.blocksize)) bytes
    from gv$sort_segment ss,
         sys.ts$ ts
    where ss.tablespace_name = ts.name
    group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND
      d.tablespace_name = t.tablespace_name(+) AND
      d.extent_management like 'LOCAL' AND
      d.contents like 'TEMPORARY';

select  df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0)  "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM   dba_TEMP_files
        GROUP  BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace
        FROM   gV$temp_extent_pool
        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+)

set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
       sid,
       serial#,
       se.sql_id
       machine,
       program,
       tablespace,
       segtype,
       (su.BLOCKS*8/1024/1024) GB
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr
 order by su.BLOCKS desc;


/*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.segtype, b.blocks, c.sql_text
     FROM v$session a, v$tempseg_usage b, v$sqlarea c
     WHERE a.saddr = b.session_addr
     AND c.address= a.sql_address
     AND c.hash_value = a.sql_hash_value
     ORDER BY b.tablespace, b.blocks;


  ※blocks列是块数，大小计算如下
    例：blocks*block_size

select distinct sample_time, session_id,program,TEMP_SPACE_ALLOCATED,
sql_id,sql_exec_start from  DBA_HIST_ACTIVE_SESS_HISTORY
 where sql_id='<SQL_ID>' order by sample_time;

select distinct sample_time, session_id,program,TEMP_SPACE_ALLOCATED,
sql_id,sql_exec_start from  GV$ACTIVE_SESSION_HISTORY 
where sql_id='<SQL_ID>'  order by sample_time;

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;

SELECT vt.inst_id,
         vs.sid,
         vs.serial#,
         vs.machine,
         vs.saddr,
         vs.program,
         vs.module,
         vs.logon_time,
         vt.tempseg_usage,
         vt.segtype
    FROM gv$session vs,
         (  SELECT inst_id,
                   username,
                   session_addr,
                   segtype,
                   ROUND (SUM (blocks) * 8192 / 1024 / 1024 / 1024, 2)
                      tempseg_usage
              FROM gv$tempseg_usage
          GROUP BY inst_id,
                   username,
                   session_addr,
                   segtype
          ORDER BY 4 DESC) vt
   WHERE vs.inst_id = vt.inst_id AND vs.saddr = vt.session_addr
ORDER BY tempseg_usage DESC;

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;

Note: 67534.1  VIEW: "V$SORT_USAGE" Reference Note
Note: 364417.1 How Can Temporary Segment Usage Be Monitored Over Time?
Note: 289894.1 Queries To Monitor Temporary Tablespace Usage
TEMPORARY Tablespaces : Tempfiles or Datafiles ? (Doc ID 160426.1)
Master Note: Troubleshooting Oracle Temporary Tablespaces (Doc ID 1524594.1)
Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)
 How Do You Find Who And What SQL Is Using Temp Segments [ID 317441.1]
How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)
ORA-1652の主な発生原因とその対処方法について(KROWN:68738) (Doc ID 1723269.1)
[10g 新機能] 一時表領域グループ(KROWN:84881) (Doc ID 1727040.1)
11g: ローカル管理一時表領域の縮小(SHRINKコマンド)(KROWN:127335) (Doc ID 1741644.1)
一時表領域(KDS:7252) (Doc ID 1772113.1)
表領域の管理(KDS:3137) (Doc ID 1771502.1)
ORA-1652 On A Temporary Tablespace With Autoextensible Files (Doc ID 303930.1)