2 条评论

1. 

   Maclean June 5, 2011

   REM Listing of temp segments
   
   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;
   
   REM Temp segment usage per session
   
   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;
   
   
   
   
   
   
   
   
   How Do You Find Who And What SQL Is Using Temp Segments?
   
   For 8.1.7 to 9.2:
   
   SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
   FROM v$session a, v$sort_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;
   
   For 10.1 and above:
   
   SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, 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;
   
   
   
   
   REM  #*****************************************************
   REM  #File Name: sort.sql
   REM  #
   REM  #Purpose:   Report Sort Statistics
   REM#
   REM  #*****************************************************
   
   SELECT  substr(vs.username,1,20) "db user",
           substr(vs.osuser,1,20)   "os user",
           substr(vsn.name,1,20)   "Type of Sort",
           vss.value
   FROM    v$session vs,
           v$sesstat vss,
           v$statname vsn
   WHERE   (vss.statistic#=vsn.statistic#) AND
           (vs.sid = vss.sid) AND
           (vsn.name like '%sort%')
   ORDER BY 2,3;
   
   Creator of Sort Segment in Oracle 8 and above
   
    SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, 
             a.username, a.osuser, a.status
    FROM     v$session a,v$sort_usage b
    WHERE    a.saddr = b.session_addr
    ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
   

   Reply
2. Maclean Liu的脚本工具盒 | Ask Maclean Oracle Blog August 8, 2012

   […] 脚本:监控并行进程状态 脚本:监控数据库中的活跃用户及其运行的SQL 脚本:监控临时表空间使用率 Script to show Active Distributed Transactions Gather DBMS_STATS Default parameter Script:Datafile […]

   Reply