VIEW: SYS.DBA_DATA_FILES

Source

View Text - Preformatted

select v.name, f.file#, ts.name,
       ts.blocksize * f.blocks, f.blocks,
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
       ts.blocksize * f.maxextend, f.maxextend, f.inc,
       ts.blocksize * (f.blocks - 1), f.blocks - 1,
       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
         decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v,
     (select * from x$kccfe
      where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))) fe
where v.file# = f.file#
  and f.spare1 is NULL
  and f.ts# = ts.ts#
  and fe.fenum = f.file#
union all
select
       v.name,f.file#, ts.name,
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#,
       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
       decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts,
     (select * from x$kccfe
      where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))) fe
where v.file# = f.file#
  and f.spare1 is NOT NULL
  and v.file# = hc.ktfbhcafno
  and hc.ktfbhctsn = ts.ts#
  and fe.fenum = f.file#

View Text - HTML Formatted

SELECT V.NAME
, F.FILE#
, TS.NAME
, TS.BLOCKSIZE * F.BLOCKS
, F.BLOCKS
, DECODE(F.STATUS$
, 1
, 'INVALID'
, 2
, 'AVAILABLE'
, 'UNDEFINED')
, F.RELFILE#
, DECODE(F.INC
, 0
, 'NO'
, 'YES')
, TS.BLOCKSIZE * F.MAXEXTEND
, F.MAXEXTEND
, F.INC
, TS.BLOCKSIZE * (F.BLOCKS - 1)
, F.BLOCKS - 1
, DECODE(FE.FETSN
, 0
, DECODE(BITAND(FE.FESTA
, 2)
, 0
, 'SYSOFF'
, 'SYSTEM')
, DECODE(BITAND(FE.FESTA
, 18)
, 0
, 'OFFLINE'
, 2
, 'ONLINE'
, 'RECOVER')) FROM SYS.FILE$ F
, SYS.TS$ TS
, SYS.V$DBFILE V
, (SELECT *
FROM X$KCCFE
WHERE (CON_ID IS NULL OR CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID'))) FE WHERE V.FILE# = F.FILE#
AND F.SPARE1 IS NULL
AND F.TS# = TS.TS#
AND FE.FENUM = F.FILE# UNION ALL SELECT V.NAME
, F.FILE#
, TS.NAME
, DECODE(HC.KTFBHCCVAL
, 0
, TS.BLOCKSIZE * HC.KTFBHCSZ
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, HC.KTFBHCSZ
, NULL)
, DECODE(F.STATUS$
, 1
, 'INVALID'
, 2
, 'AVAILABLE'
, 'UNDEFINED')
, F.RELFILE#
, DECODE(HC.KTFBHCCVAL
, 0
, DECODE(HC.KTFBHCINC
, 0
, 'NO'
, 'YES')
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, TS.BLOCKSIZE * HC.KTFBHCMAXSZ
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, HC.KTFBHCMAXSZ
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, HC.KTFBHCINC
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, HC.KTFBHCUSZ * TS.BLOCKSIZE
, NULL)
, DECODE(HC.KTFBHCCVAL
, 0
, HC.KTFBHCUSZ
, NULL)
, DECODE(FE.FETSN
, 0
, DECODE(BITAND(FE.FESTA
, 2)
, 0
, 'SYSOFF'
, 'SYSTEM')
, DECODE(BITAND(FE.FESTA
, 18)
, 0
, 'OFFLINE'
, 2
, 'ONLINE'
, 'RECOVER')) FROM SYS.V$DBFILE V
, SYS.FILE$ F
, SYS.X$KTFBHC HC
, SYS.TS$ TS
, (SELECT *
FROM X$KCCFE
WHERE (CON_ID IS NULL OR CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID'))) FE WHERE V.FILE# = F.FILE#
AND F.SPARE1 IS NOT NULL
AND V.FILE# = HC.KTFBHCAFNO
AND HC.KTFBHCTSN = TS.TS#
AND FE.FENUM = F.FILE#