Algunos Comandos para manejo de la TEMP en Oracle

Estos normalmente los uso para identificar procesos que consumen mas de la TEMP facilitando su depuración y evitando un congelamiento

 ---- buscar sesión con mas uso de temp

SET LINES 2000

SET PAGES 2000

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN FILE_NAME FORMAT A55

SELECT   b.TABLESPACE

       , b.segfile#

       , b.segblk#

       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb

       , a.inst_ID

       , a.SID

       , a.serial#

       , a.username

       , a.osuser

       , a.program

       , a.status

   , a.machine

    FROM gv$session a

       , gv$sort_usage b

       , gv$process c

       , gv$parameter p

   WHERE p.NAME = 'db_block_size'

     AND a.saddr = b.session_addr

     AND a.paddr = c.addr

--AND A.PROGRAM LIKE ('%ALGUIEN%')

     -- AND b.TABLESPACE='TEMP2'

ORDER BY 4 desc;

a.inst_ID , b.TABLESPACE

       , b.segfile#

       , b.segblk#

       , b.blocks;

   

--- mostrar uso temp por sesión

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, S.machine,P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

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, S.machine, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

---- mostrar uso temp por sentencia

SET LINES 2000

SET PAGES 2000

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, s.machine, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE    T.session_addr = S.saddr

AND      T.sqladdr = Q.address (+)

AND      T.tablespace = TBS.tablespace_name

-- AND Q.sql_text like ('%job_%')

ORDER BY 4 desc;

S.sid;

--- espacio temp pre 11

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;