select fe.inst_id,
       fe.fenum,
       to_number(fe.fecrc_scn),
       to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
       fe.fetsn,
       fe.ferfn,
       decode(fe.fetsn,
               0,
               decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
               decode(bitand(fe.festa, 18),
                       0,
                       'OFFLINE',
                       2,
                       'ONLINE',
                       'RECOVER')),
       decode(fe.fedor,
               2,
               'READ ONLY',
               decode(bitand(fe.festa, 12),
                       0,
                       'DISABLED',
                       4,
                       'READ ONLY',
                       12,
                       'READ WRITE',
                       'UNKNOWN')),
       to_number(fe.fecps),
       to_date(fe.fecpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       to_number(fe.feurs),
       to_date(fe.feurt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       to_number(fe.fests),
       decode(fe.f ests,
              NULL,
              to_date(NULL),
              to_date(fe.festt,
                      'MM/DD/RR HH24:MI:SS',
                      'NLS_CALENDAR=Gregorian')),
       to_number(fe.feofs),
       to_numb er(fe.feonc_scn),
       to_date(fe.feonc_tim,
               'MM/DD/RR HH24:MI:SS',
               'NLS_CALENDAR=Gregorian'),
       fh.fhfsz * fe.febsz,    --BYTES
       fh.fhfsz,               --BLOCKS
       fe.fe csz * fe.febsz,  --CREATE_BYTES
       fe.febsz,  --BLOCK_SIZE
       fn.fnnam,  --NAME
       fe.fefdb,
       fn.fnbof,
       decode(fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
       to_ number(fh.fhfirstunrecscn),
       to_date(fh.fhfirstunrectime,
                'MM/DD/RR HH24:MI:SS',
                'NLS_CALENDAR=Gregorian'),
       fe.fepdi,
       fe.fefcrs,
       fe.fefcrt,
       decode(fe.fefdb, 1, 'YES', 'NO'),
       fe.feplus,
       fe.feprls,
       fe.feprlt fr om x$kccfe fe,
       x$kccfn fn,
       x$kccfn fnaux,
       x$kcvfh fh
 where ((fe.fepax != 65535 and fe.fepax != 0 and fe.fepax = fnaux.fnnum) or
       ((fe.fepax = 65535 or fe.fepax = 0) and fe.
        fenum = fnaux.fnfno and fnaux.fntyp = 4 and fnaux.fnnam is not null and bita
        nd(fnaux.fnflg, 4) != 4 and fe.fefnh = fnaux.fnnum))
   and fn.fnfno = fe.fenum
   and fn.fnfno = fh.hxfil
   and fe.fefnh = fn.fnnum
   and fe.fedup != 0
   and fn.fntyp = 4
   and fn.fnnam is not null
   and bitand(fn.fnflg, 4) != 4
 order by fe.fenum

#根据文件名查文件号,及size 

select fe.fenum,fn.fnnam,(fh.fhfsz * fe.febsz)/(1024*1024*1024)
 from x$kccfe fe, x$kccfn fn,x$kcvfh fh
 where
   fe.fefnh = fn.fnnum
    and fn.fnfno = fe.fenum
    and fn.fnfno = fh.hxfil
    and fe.fedup != 0
   and fn.fntyp = 4
        and fn.fnnam is not null
     and bitand(fn.fnflg, 4) != 4
     and fn.fnnam in (
     '+DATA/start/datafile/isbapp463',
     '+DATA/start/datafile/isbapp464',
     '+DATA/start/datafile/isbapp465',
     '+DATA/start/datafile/isbapp459',
     '+DATA/start/datafile/isbapp460'