select
  s.inst_id,                               -- INST_ID NUMBER
  s.kwqpsqid,                              -- QUEUE_ID NUMBER
  q.queue_schema,                          -- QUEUE_SCHEMA VARCHAR2(30)
  q.queue_name,                            -- QUEUE_NAME VARCHAR2(30)
  s.kwqpsdqs,                              -- DST_QUEUE_SCHEMA VARCHAR2(30)
  s.kwqpsdqn,                              -- DST_QUEUE_NAME VARCHAR2(30)
  s.kwqpstim,                              -- STARTUP_TIME DATE
  s.kwqpsdbn,                              -- DBLINK VARCHAR2(128)
  s.kwqpshwm,                              -- HIGH_WATER_MARK NUMBER
  s.kwqpsack,                              -- ACKNOWLEDGEMENT NUMBER
  s.kwqpsstt,                              -- SCHEDULE_STATUS VARCHAR2(30)
  s.kwqpsmsg,                              -- TOTAL_MSGS NUMBER
  s.kwqpsbyt,                              -- TOTAL_BYTES NUMBER
  s.kwqpsdeq/10000,                        -- ELAPSED_DEQUEUE_TIME NUMBER
  s.kwqpspic/10000,                        -- ELAPSED_PICKLE_TIME NUMBER
  (s.kwqpsprp - s.kwqpsdeq - s.kwqpspic)/10000,-- ELAPSED_PROPAGATION_TIME NUMBER
  s.kwqpsrl/10000,                         -- ELAPSED_RULE_TIME NUMBER
  s.kwqpsmmsg,                             -- MAX_NUM_PER_WIN NUMBER
  s.kwqpsmbyt,                             -- MAX_SIZE NUMBER
  s.kwqpslmsglat,                          -- LAST_MSG_LATENCY NUMBER
  s.kwqpslmsgenqtime,                      -- LAST_MSG_ENQUEUE_TIME TIMESTAMP(6)
  CAST(                                    -- LAST_MSG_PROPAGATION_TIME TIMESTAMP(6)
    s.kwqpslmsgenqtime + s.kwqpslmsglat/86400 AS TIMESTAMP),
  s.kwqpsllcrlat,                          -- LAST_LCR_LATENCY NUMBER
  s.kwqpsllcrts,                           -- LAST_LCR_CREATION_TIME DATE
  s.kwqpsllcrts + s.kwqpsllcrlat/86400,    -- LAST_LCR_PROPAGATION_TIME DATE
  s.kwqpsddbnm,                            -- DST_DATABASE_NAME VARCHAR2(128)
  s.kwqpssid,                              -- SESSION_ID NUMBER
  s.kwqpsser,                              -- SERIAL# NUMBER
  s.kwqpsospid,                            -- SPID VARCHAR2(24)
  s.kwqpsprpn,                             -- PROPAGATION_NAME VARCHAR2(30)
  decode(                                  -- STATE VARCHAR2(53)
    s.kwqpsstt, 'SCHEDULE OPTIMIZED', decode(kwqpsstate, 0, 'Initializing', 1, 'Capturing change records', 2, 'Evaluating rules', 3, 'Enqueueing LCRs', 4, 'Shutting down', 5, 'Aborting', 6, 'Creating LCRs', 7, 'Waiting for dictionary dump', 8, 'Waiting for redo log', 9, 'Pausing for flow control', 10, 'Processing dictionary dump', 11, 'Waiting for apply to be enabled', 12, 'Waiting for apply database to start', 13, 'Waiting for propagation to be enabled', 14, 'Waiting for previously connected capture to terminate', 15, 'Waiting for a subscriber to be added', 16, 'Suspended due to a dropped subscriber', 17, 'Suspended for auto split/merge', 18, 'Waiting on empty queue', 100, 'Initializing', 101, 'Initializing propagation receiver', 102, 'Browsing LCRs', 103, 'Evaluating rules', 104, 'Dequeueing LCRs', 105, 'Sending LCRs', 111, 'Waiting for apply to be enabled', 112, 'Waiting for apply database to start', 113, 'Waiting for propagation to be enabled', 114, 'Waiting for capture to terminate', 115, 'Waiting for a subscriber to be added', 116, 'Suspended due to a dropped subscriber', 117, 'Suspended for auto split/merge', 118, 'Waiting on empty queue', null), s.kwqpsstt) 
from
  x$kwqps s,
  x$buffered_queues q
where
  s.inst_id = q.inst_id and s.kwqpsqid = q.queue_id and bitand(q.flags, 16) = 0