脚本：监控临时表空间使用率

select (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='TEMP') s,
(select sum(blocks) total_blocks from dba_data_files where tablespace_name='TEMP') f;

select (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='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

 select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
  2  from (select sum(used_blocks) tot_used_blocks from
v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

percent used
------------
           0

Oracle, Oracle脚本script   Oracle脚本script, temporary segment, temporary tablespace  

1 comment to 脚本：监控临时表空间使用率

• Maclean

  June 5, 2011 at 10:43 PM · Reply

  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;
  

Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved
Powered by WordPress & Atahualpa