VIEW: SYS.DBA_TEMP_FILES

Source

View Text - Preformatted

select /*+ ordered use_nl(hc) */
       v.fnnam, hc.ktfthctfno, ts.name,
       decode(hc.ktfthccval, 0, ts.blocksize * hc.ktfthcsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcsz, NULL),
       decode(bitand(tf.tfsta, 2),0,'OFFLINE',2,'ONLINE','UNKNOWN'),
       decode(hc.ktfthccval, 0, hc.ktfthcfno, NULL),
       decode(hc.ktfthccval, 0, decode(hc.ktfthcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfthccval, 0, ts.blocksize * hc.ktfthcmaxsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcmaxsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcinc, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcusz * ts.blocksize, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcusz, NULL)
from sys.x$kccfn v, sys.x$ktfthc hc, sys.ts$ ts,
     (select * from  sys.x$kcctf
      where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))) tf
where v.fntyp = 7 and v.fnnam is not null
  and v.fnfno = hc.ktfthctfno
  and hc.ktfthctsn = ts.ts#
  and v.fnfno = tf.tfnum
  and tf.tffnh = v.fnnum
  and tf.tfdup != 0
  and bitand(tf.tfsta, 32) <> 32

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(HC) */ V.FNNAM
, HC.KTFTHCTFNO
, TS.NAME
, DECODE(HC.KTFTHCCVAL
, 0
, TS.BLOCKSIZE * HC.KTFTHCSZ
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCSZ
, NULL)
, DECODE(BITAND(TF.TFSTA
, 2)
, 0
, 'OFFLINE'
, 2
, 'ONLINE'
, 'UNKNOWN')
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCFNO
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, DECODE(HC.KTFTHCINC
, 0
, 'NO'
, 'YES')
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, TS.BLOCKSIZE * HC.KTFTHCMAXSZ
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCMAXSZ
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCINC
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCUSZ * TS.BLOCKSIZE
, NULL)
, DECODE(HC.KTFTHCCVAL
, 0
, HC.KTFTHCUSZ
, NULL) FROM SYS.X$KCCFN V
, SYS.X$KTFTHC HC
, SYS.TS$ TS
, (SELECT *
FROM SYS.X$KCCTF
WHERE (CON_ID IS NULL OR CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID'))) TF WHERE V.FNTYP = 7
AND V.FNNAM IS NOT NULL
AND V.FNFNO = HC.KTFTHCTFNO
AND HC.KTFTHCTSN = TS.TS#
AND V.FNFNO = TF.TFNUM
AND TF.TFFNH = V.FNNUM
AND TF.TFDUP != 0
AND BITAND(TF.TFSTA
, 32) <> 32