select /*+ rule */ fe.inst_id, -- INST_ID NUMBER fe.fenum, -- FILE# NUMBER to_number -- CREATION_CHANGE# NUMBER (fe.fecrc_scn), 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 -- ONLINE_CHANGE# NUMBER (fe.feonc_scn), 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 -- FIRST_NONLOGGED_SCN NUMBER (fh.fhfirstunrecscn), to_date -- FIRST_NONLOGGED_TIME DATE (fh.fhfirstunrectime, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') 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

home |  up  |  mailbox