oracle 4

临时表空间使用率：
set pages 800
set lines 150
set feedback off
--Total Used
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    gv$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
/




--Used by Session
set line 400
col username format a20
SELECT s.inst_id, s.username, s.sid, s.serial#, u.tablespace,
 sum(u.blocks * ts.block_size)/1024/1024 mb_used
FROM gv$session s, gv$sort_usage u, dba_tablespaces ts
WHERE s.saddr = u.session_addr
 and s.inst_id = u.inst_id
 and u.tablespace = ts.tablespace_name
GROUP by s.inst_id, s.username, s.sid, s.serial#, u.tablespace
ORDER by 6
/


(2)监控每个会话使用了多少临时表空间
select se.inst_id, se.sid,se.SERIAL#,tablespace, sum(tu.blocks)*32/1024/1024,SE.USERNAME
  from gv$tempseg_usage tu, gv$session se
 where tu.inst_id = se.inst_id
   and tu.session_addr = se.saddr
   and tu.session_num = se.serial#
 group by se.inst_id, se.sid,se.SERIAL#,tablespace,SE.USERNAME
 order by 5 desc;


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;