select
  rt.inst_id,                              -- INST_ID NUMBER
  rtnum,                                   -- THREAD# NUMBER
  decode(                                  -- STATUS VARCHAR2(6)
    bitand(rtsta,1),1,'OPEN','CLOSED'),
  decode(                                  -- ENABLED VARCHAR2(8)
    bitand(rtsta,6),0,'DISABLED',2,'PRIVATE',6,'PUBLIC','UNKNOWN'),
  rtnlf,                                   -- GROUPS NUMBER
  tirsid,                                  -- INSTANCE VARCHAR2(80)
  to_date(                                 -- OPEN_TIME DATE
    rtots,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
  rtcln,                                   -- CURRENT_GROUP# NUMBER
  rtseq,                                   -- SEQUENCE# NUMBER
  to_number(rtckp_scn),                    -- CHECKPOINT_CHANGE# NUMBER
  to_date(                                 -- CHECKPOINT_TIME DATE
    rtckp_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
  to_number(rtenb),                        -- ENABLE_CHANGE# NUMBER
  to_date(                                 -- ENABLE_TIME DATE
    rtets,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
  to_number(rtdis),                        -- DISABLE_CHANGE# NUMBER
  to_date(                                 -- DISABLE_TIME DATE
    rtdit,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
  cpodr_seq,                               -- LAST_REDO_SEQUENCE# NUMBER
  cpodr_bno,                               -- LAST_REDO_BLOCK NUMBER
  to_number(cpods),                        -- LAST_REDO_CHANGE# NUMBER
  to_date(                                 -- LAST_REDO_TIME DATE
    cpodt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') 
from
  x$kccrt rt,
  x$kcctir tr,
  x$kcccp
where
  rtnlf != 0 and tr.inst_id = rt.inst_id and tirnum = rtnum and cptno = rtnum