select
tf.inst_id, -- INST_ID NUMBER
tf.tfnum, -- FILE# NUMBER
to_number(tf.tfcrc_scn), -- CREATION_CHANGE# NUMBER
to_date( -- CREATION_TIME DATE
tf.tfcrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
tf.tftsn, -- TS# NUMBER
tf.tfrfn, -- RFILE# NUMBER
decode( -- STATUS VARCHAR2(7)
bitand(tf.tfsta, 2),0,'OFFLINE',2,'ONLINE','UNKNOWN'),
decode( -- ENABLED VARCHAR2(10)
bitand(tf.tfsta, 12), 0,'DISABLED',4, 'READ ONLY', 12, 'READ WRITE', 'UNKNOWN'),
fh.fhtmpfsz*tf.tfbsz, -- BYTES NUMBER
fh.fhtmpfsz, -- BLOCKS NUMBER
tf.tfcsz*tf.tfbsz, -- CREATE_BYTES NUMBER
tf.tfbsz, -- BLOCK_SIZE NUMBER
fn.fnnam -- NAME VARCHAR2(513)
from
x$kcctf tf,
x$kccfn fn,
x$kcvfhtmp fh
where
fn.fnfno=tf.tfnum and fn.fnfno=fh.htmpxfil and tf.tffnh=fn.fnnum and tf.tfdup!=0 and bitand(tf.tfsta, 32) <> 32 and fn.fntyp=7 and fn.fnnam is not null