Oracle九大性能视图 v$sort_usage_temp表空间的使用情况

1、表结构ITPUB个人空间)e:T}/Ag[;zf`M
SQL> desc v$sort_usageITPUB个人空间$YrRIO9^�XZ:S4`4WO
 名称                                      是否为空? 类型ITPUB个人空间%a� n"n6Q4m4B
 ----------------------------------------- -------- ----------------------------
3p U~ ca;~#Rt@0 USERNAME                                           VARCHAR2(30)
!N-ZH fi0 USER                                               VARCHAR2(30)ITPUB个人空间3t3RC7r;[
 SESSION_ADDR                                       RAW(8)ITPUB个人空间Sds} T$n`
p:w;A
 SESSION_NUM                                        NUMBERITPUB个人空间 d {H:@!]v3Rp8ow
 SQLADDR                                            RAW(8)
;kcze6hL,N#RD0 SQLHASH                                            NUMBER
'v3K
N4qR0 SQL_ID                                             VARCHAR2(13)
*y5h/x B(mU,dz)Pn!r,}K0 TABLESPACE                                         VARCHAR2(31)
[ m*P7|&qn6D&K#k0 CONTENTS                                           VARCHAR2(9)
g-S}E-e|$v Fu0 SEGTYPE                                            VARCHAR2(9)ITPUB个人空间(r8[ k9UzD!O%@3V;F}k
 SEGFILE#                                           NUMBER
7?W8N8w8_!ODF0 SEGBLK#                                            NUMBERITPUB个人空间 ~7Y!k!Iq"i#r�W#c
 EXTENTS                                            NUMBERITPUB个人空间7[1|pL
 BLOCKS                                             NUMBER
4c] s-I2i@6y0 SEGRFNO#                                           NUMBER
@;b.SD q} Y h0 ITPUB个人空间tQG0Xq0j
 2、SQL语句ITPUB个人空间N(|K6QNm
 ITPUB个人空间 ^9[q+h3LF:~Y�{._E-_
 temp表空间的使用情况，当temp表空间变得巨大的时候，根据session_addr可以得到session id，根据sqladdr和sqlhash可以得到正在执行的sql：

select se.username,
/r$}"ku@0       se.sid,ITPUB个人空间H`yyu+TVn~
       su.extents,
#F j)X2BV$I�X+g.g}0       su.blocks * to_number(rtrim(p.value)) as Space,
o1~8^7e|0       tablespace,ITPUB个人空间
b P|emN |Wv{
       segtype,
6cy%G3EBA0       sql_textITPUB个人空间M6B&]0G'N/r}
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
1A!q�eYuo-B0 where p.name = 'db_block_size'
4d}dK"~V#{9s
y0   and su.session_addr = se.saddrITPUB个人空间F#j!x$p'G!Rc
   and s.hash_value = su.sqlhash
Y!E,f3I1e0   and s.address = su.sqladdrITPUB个人空间'q0Ei#c,g#D6[&O
 order by se.username, se.sid;ITPUB个人空间@ z2U:xy8NMa�z&R
 
o8@tpd
o(_0 查询结果如下：ITPUB个人空间�GgK3IS/TY`
 
#|:yI9J,e q0    USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXTITPUB个人空间Y4A.f1Sx2i,Q
1 SYS 145 1 1048576 TEMP DATA select value from v$sesstat where sid = :sid order by statistic#ITPUB个人空间S%MW N*[-Jj
2 SYS 145 1 1048576 TEMP INDEX select value from v$sesstat where sid = :sid order by statistic#

Fd�_YOX+E$@03 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
/C6|9Vg]3J3Qt01 1 1 519177 2010-4-9 12:51:42 3 1 ONLINE READ WRITE 33554432 4096 20971520 8192 D:ORACLE64PRODUCT10.2.0ORADATACSDBTEMP01.DBF

4、查看谁在用临时表空间

SELECT se.username,se.sid,ITPUB个人空间
{?@#x*AFek
　　se.serial#,
w6n1k0y L|v:E9i0　　se.sql_address,ITPUB个人空间(vZ5C ~/b(fN3X2h
　　se.machine,ITPUB个人空间{6~+H'p/kz
　　se.program,
P`.v4x$W0　　su.tablespace,
3t0]$f�?'zzf0　　su.segtype,
l2o$M w%R7Q0　　su.contents
f/?yW.y0　　FROM v$session se,ITPUB个人空间:bY,XA?zu tz6D
　　v$sort_usage suITPUB个人空间u-I:f5A%ZR/mG,D
　　WHERE se.saddr=su.session_addr;

结果如下：

    USERNAME SID SERIAL# SQL_ADDRESS MACHINE PROGRAM TABLESPACE SEGTYPE CONTENTS
:h7W1`-d.WM{01 SYS 145 15 00 WORKGROUPZHOULINLING plsqldev.exe TEMP DATA TEMPORARY
2ly5D7Pf02 SYS 145 15 00 WORKGROUPZHOULINLING plsqldev.exe TEMP LOB_DATA TEMPORARYITPUB个人空间^,r,k+RG1@9u?
3 SYS 145 15 00 WORKGROUPZHOULINLING 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;

http://space.itpub.net/?uid-9664900-action-viewspace-itemid-676768