???????????????????

???????????? ???????[ 2013/6/17 10:14:34 ] ????????

????3????????????С?????????

SELECT t1."Tablespace" "Tablespace"??
       t1."Total (G)" "Total (G)"??
       nvl(t2."Used (G)"?? 0) "Used(G)"??
       t1."Total (G)" - nvl(t2."Used (G)"?? 0) "Free (G)"
FROM   (SELECT tablespace_name "Tablespace"??
               to_char((SUM(bytes / 1024 / 1024 / 1024))?? '99??999??990.900') "Total (G)"
        FROM   dba_temp_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name "Tablespace"??
               to_char((SUM(bytes / 1024 / 1024 / 1024))?? '99??999??990.900') "Total (G)"
        FROM   dba_data_files
        WHERE tablespace_name LIKE 'TEMP%'
        GROUP BY tablespace_name) t1??
       (SELECT tablespace?? round(SUM(blocks) * 8 / 1024 /1024) "Used (G)"
        FROM   v$sort_usage
        GROUP BY tablespace) t2
WHERE t1."Tablespace" = t2.tablespace(+);


Tablespace                     Total (G)          Used(G)   Free (G)
------------------------------ --------------- ---------- ----------
GOEX_TEMP                               31.999          1     30.999
FIX_TEMP                                 0.098          0       .098
TEMP                                     0.195          0       .195

????4??????????????????С?????????????????sql???

-->???????ε?SQL???
SELECT sess.SID?? segtype?? blocks * 8 / 1000 "MB"?? sql_text
FROM   v$sort_usage sort?? v$session sess?? v$sql SQL
WHERE sort.SESSION_ADDR = sess.SADDR
        AND SQL.ADDRESS = sess.SQL_ADDRESS
ORDER BY blocks DESC;

-->???????????????????????????
col username format a15
col machine format a15
col program format a30
col tablespace format a15
set linesize 160
SELECT s.username
       ??s.sid
       ??s.serial#
       ??s.sql_address
       ??s.machine
       ??s.program
       ??su.tablespace
       ??su.segtype
       ??su.contents
FROM   v$session s?? v$sort_usage su
WHERE s.saddr = su.session_addr;

SELECT 'the ' || NAME || ' temp tablespaces ' || tablespace_name ||
       ' idle ' ||
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100?? 3) ||
       '% at ' || to_char(SYSDATE?? 'yyyymmddhh24miss')
FROM   (SELECT d.tablespace_name tablespace_name??
               nvl(SUM(used_blocks)?? 0) tot_used_blocks??
               SUM(blocks) total_blocks
        FROM   v$sort_segment v?? dba_temp_files d
        WHERE d.tablespace_name = v.tablespace_name(+)
        GROUP BY d.tablespace_name) s??
       v$database;