【Oracle九大效能檢視】之8.v$sort_usage temp表空間的使用情況

1、表結構
SQL> desc v$sort_usage
名稱                                      是否為空? 型別
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)
USER                                               VARCHAR2(30)
SESSION_ADDR                                       RAW(8)
SESSION_NUM                                        NUMBER
SQLADDR                                            RAW(8)
SQLHASH                                            NUMBER
SQL_ID                                             VARCHAR2(13)
TABLESPACE                                         VARCHAR2(31)
CONTENTS                                           VARCHAR2(9)
SEGTYPE                                            VARCHAR2(9)
SEGFILE#                                           NUMBER
SEGBLK#                                            NUMBER
EXTENTS                                            NUMBER
BLOCKS                                             NUMBER
SEGRFNO#                                           NUMBER

2、SQL語句

temp表空間的使用情況，當temp表空間變得巨大的時候，根據session_addr可以得到session id，根據sqladdr和sqlhash可以得到正在執行的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;

查詢結果如下：

    USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
1 SYS 145 1 1048576 TEMP DATA select value from v$sesstat where sid = :sid order by statistic#
2 SYS 145 1 1048576 TEMP INDEX select value from v$sesstat where sid = :sid order by statistic#
3 SYS 145 1 1048576 TEMP LOB_DATA select value from v$sesstat where sid = :sid order by statistic#
3、查臨時檔案情況
SELECT tf.inst_id, tf.tfnum, TO_NUMBER (tf.tfcrc_scn),       TO_DATE (tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),       tf.tftsn, tf.tfrfn,       DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),       DECODE (BITAND (tf.tfsta, 12),               0, 'DISABLED',               4, 'READ ONLY',               12, 'READ WRITE',               'UNKNOWN'              ),       fh.fhtmpfsz * tf.tfbsz, fh.fhtmpfsz, tf.tfcsz * tf.tfbsz, tf.tfbsz,       fn.fnnam  FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh WHERE fn.fnfno = tf.tfnum   AND fn.fnfno = fh.htmpxfil   AND tf.tffnh = fn.fnnum   AND tf.tfdup != 0   AND fn.fntyp = 7   AND fn.fnnam IS NOT NULL
結果如下：
    INST_ID TFNUM TO_NUMBER(TF.TFCRC_SCN) TO_DATE(TF.TFCRC_TIM,'MM/DD/RR TFTSN TFRFN DECODE(BITAND(TF.TFSTA,2),0,'O DECODE(BITAND(TF.TFSTA,12),0,' FH.FHTMPFSZ*TF.TFBSZ FHTMPFSZ TF.TFCSZ*TF.TFBSZ TFBSZ FNNAM
1 1 1 519177 2010-4-9 12:51:42 3 1 ONLINE READ WRITE 33554432 4096 20971520 8192 D:\ORACLE64\PRODUCT\10.2.0\ORADATA\CSDB\TEMP01.DBF
4、檢視誰在用臨時表空間
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;
結果如下：
    USERNAME SID SERIAL# SQL_ADDRESS MACHINE PROGRAM TABLESPACE SEGTYPE CONTENTS
1 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP DATA TEMPORARY
2 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP LOB_DATA TEMPORARY
3 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP INDEX TEMPORARY
5、檢視臨時表空間temp空閒情況
select TABLESPACE_NAME,file_id,bytes_used/1024/1024,bytes_free/1024/1024 from v$TEMP_SPACE_HEADER;