VIEW: SYS.DBA_APPLY

Source

View Text - Preformatted

select ap.apply_name, ap.queue_name, ap.queue_owner,
       decode(bitand(ap.flags, 1), 1, 'YES',
                                   0, 'NO'),
       ap.ruleset_name, ap.ruleset_owner,
       u.name, ap.apply_dblink, ap.apply_tag, ap.ddl_handler,
       ap.precommit_handler, ap.message_handler,
       -- if uncommitted XOut show capture's status else show apply status
       case when (bitand(ap.flags, 1280) = 1280)
         then (select decode(cp.status, 1, 'DISABLED',
                         2, 'ENABLED',
                         4, 'ABORTED', 'UNKNOWN')
               from sys.streams$_capture_process cp, sys.xstream$_server xs
                 where ap.apply_name = xs.server_name and
                       xs.queue_owner = cp.queue_owner and
                       xs.queue_name = cp.queue_name)
         else decode(ap.status,
                         1, 'DISABLED',
                         2, 'ENABLED',
                         4, 'ABORTED', 'UNKNOWN') end,
       ap.spare1,
       ap.negative_ruleset_name, ap.negative_ruleset_owner,
       ap.status_change_time, ap.error_number, ap.error_message,
       decode(bitand(ap.flags, 1), 1, 'CAPTURED',
              decode(bitand(ap.flags, 128), 128, 'CAPTURED',
                                              0, 'PERSISTENT')),
       -- if uncommitted XOut, purpose can only be 'GoldenGate'
       (case
          when (bitand(ap.flags, 1280)     = 1280)  then 'GoldenGate Capture'
          when (bitand(ap.flags, 16)       = 16)    then 'CHANGE DATA CAPTURE'
          when (bitand(ap.flags, 32)       = 32)    then 'AUDIT VAULT'
          when (bitand(ap.flags, 16384)    = 16384) then
            (case
               when (bitand(ap.flags, 256) = 256)   then 'GoldenGate Capture'
               when (bitand(ap.flags, 512) = 512)   then 'GoldenGate Apply'
             end)
          when (bitand(ap.flags, 256)      = 256)   then 'XStream Out'
          when (bitand(ap.flags, 512)      = 512)   then 'XStream In'
          else
             ( select 'XStream Streams' from dual where exists
                (select 1 from sys.props$
                  where name = 'GG_XSTREAM_FOR_STREAMS' and value$ = 'T')
               union
               select 'Streams' from dual where NOT exists
                (select 1 from sys.props$
                  where name = 'GG_XSTREAM_FOR_STREAMS' and value$ = 'T'))
       end)
  from "_DBA_APPLY" ap, sys.user$ u
 where  ap.apply_userid = u.user# (+)

View Text - HTML Formatted

SELECT AP.APPLY_NAME
, AP.QUEUE_NAME
, AP.QUEUE_OWNER
, DECODE(BITAND(AP.FLAGS
, 1)
, 1
, 'YES'
, 0
, 'NO')
, AP.RULESET_NAME
, AP.RULESET_OWNER
, U.NAME
, AP.APPLY_DBLINK
, AP.APPLY_TAG
, AP.DDL_HANDLER
, AP.PRECOMMIT_HANDLER
, AP.MESSAGE_HANDLER
, -- IF UNCOMMITTED XOUT SHOW CAPTURE'S STATUS ELSE SHOW APPLY STATUS CASE WHEN (BITAND(AP.FLAGS
, 1280) = 1280) THEN (SELECT DECODE(CP.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN')
FROM SYS.STREAMS$_CAPTURE_PROCESS CP
, SYS.XSTREAM$_SERVER XS
WHERE AP.APPLY_NAME = XS.SERVER_NAME AND XS.QUEUE_OWNER = CP.QUEUE_OWNER AND XS.QUEUE_NAME = CP.QUEUE_NAME) ELSE DECODE(AP.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN') END
, AP.SPARE1
, AP.NEGATIVE_RULESET_NAME
, AP.NEGATIVE_RULESET_OWNER
, AP.STATUS_CHANGE_TIME
, AP.ERROR_NUMBER
, AP.ERROR_MESSAGE
, DECODE(BITAND(AP.FLAGS
, 1)
, 1
, 'CAPTURED'
, DECODE(BITAND(AP.FLAGS
, 128)
, 128
, 'CAPTURED'
, 0
, 'PERSISTENT'))
, -- IF UNCOMMITTED XOUT
, PURPOSE CAN ONLY BE 'GOLDENGATE' (CASE WHEN (BITAND(AP.FLAGS
, 1280) = 1280) THEN 'GOLDENGATE CAPTURE' WHEN (BITAND(AP.FLAGS
, 16) = 16) THEN 'CHANGE DATA CAPTURE' WHEN (BITAND(AP.FLAGS
, 32) = 32) THEN 'AUDIT VAULT' WHEN (BITAND(AP.FLAGS
, 16384) = 16384) THEN (CASE WHEN (BITAND(AP.FLAGS
, 256) = 256) THEN 'GOLDENGATE CAPTURE' WHEN (BITAND(AP.FLAGS
, 512) = 512) THEN 'GOLDENGATE APPLY' END) WHEN (BITAND(AP.FLAGS
, 256) = 256) THEN 'XSTREAM OUT' WHEN (BITAND(AP.FLAGS
, 512) = 512) THEN 'XSTREAM IN' ELSE ( SELECT 'XSTREAM STREAMS'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM SYS.PROPS$
WHERE NAME = 'GG_XSTREAM_FOR_STREAMS'
AND VALUE$ = 'T') UNION SELECT 'STREAMS'
FROM DUAL
WHERE NOT EXISTS (SELECT 1
FROM SYS.PROPS$
WHERE NAME = 'GG_XSTREAM_FOR_STREAMS'
AND VALUE$ = 'T')) END)
FROM "_DBA_APPLY" AP
, SYS.USER$ U
WHERE AP.APPLY_USERID = U.USER# (+)