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'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-618419/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/94384/viewspace-618419/