select
fe.inst_id, -- INST_ID NUMBER
fe.fenum, -- FILE# NUMBER
to_number(fe.fecrc_scn), -- CREATION_CHANGE# NUMBER
to_date( -- CREATION_TIME DATE
fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fe.fetsn, -- TS# NUMBER
fe.ferfn, -- RFILE# NUMBER
decode( -- STATUS VARCHAR2(7)
fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')),
decode( -- ENABLED VARCHAR2(10)
fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')),
to_number(fe.fecps), -- CHECKPOINT_CHANGE# NUMBER
to_date( -- CHECKPOINT_TIME DATE
fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.feurs), -- UNRECOVERABLE_CHANGE# NUMBER
to_date( -- UNRECOVERABLE_TIME DATE
fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.fests), -- LAST_CHANGE# NUMBER
decode( -- LAST_TIME DATE
fe.fests,NULL,to_date(NULL), to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),
to_number(fe.feofs), -- OFFLINE_CHANGE# NUMBER
to_number(fe.feonc_scn), -- ONLINE_CHANGE# NUMBER
to_date( -- ONLINE_TIME DATE
fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fh.fhfsz*fe.febsz, -- BYTES NUMBER
fh.fhfsz, -- BLOCKS NUMBER
fe.fecsz*fe.febsz, -- CREATE_BYTES NUMBER
fe.febsz, -- BLOCK_SIZE NUMBER
fn.fnnam, -- NAME VARCHAR2(513)
fe.fefdb, -- PLUGGED_IN NUMBER
fn.fnbof, -- BLOCK1_OFFSET NUMBER
decode( -- AUX_NAME VARCHAR2(513)
fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
to_number(fh.fhfirstunrecscn), -- FIRST_NONLOGGED_SCN NUMBER
to_date( -- FIRST_NONLOGGED_TIME DATE
fh.fhfirstunrectime,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fe.fepdi, -- FOREIGN_DBID NUMBER
fe.fefcrs, -- FOREIGN_CREATION_CHANGE# NUMBER
fe.fefcrt, -- FOREIGN_CREATION_TIME DATE
decode( -- PLUGGED_READONLY VARCHAR2(3)
fe.fefdb, 1, 'YES', 'NO'),
fe.feplus, -- PLUGIN_CHANGE# NUMBER
fe.feprls, -- PLUGIN_RESETLOGS_CHANGE# NUMBER
fe.feprlt -- PLUGIN_RESETLOGS_TIME DATE
from
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 bitand(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