トランザクション順に取得
SELECT
*
FROM
(
SELECT
SCN,
MIN(START_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as START_SCN,
MAX(COMMIT_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as COMMIT_SCN,
USERNAME,
RAWTOHEX(xid) as xid,
SEQUENCE#,
to_char(MIN(START_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)), 'yyyy/mm/dd HH24:MI:SS') as START_TIMESTAMP,
to_char(MAX(COMMIT_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)), 'yyyy/mm/dd HH24:MI:SS') as COMMIT_TIMESTAMP,
SQL_REDO,
OPERATION_CODE,
MIN(OPERATION_CODE) OVER (PARTITION BY RAWTOHEX(xid) ) as OPE_CODE_MIN
FROM V$LOGMNR_CONTENTS
WHERE
sql_redo like upper('%streams$_apply_milestone%') -- ★SQL文で絞る場合
--USERNAME in ('HR') -- ★ユーザで絞る場合
--AND (SEG_OWNER <> 'SYS' or SEG_OWNER is null) -- ★SYSユーザを除外する場合
AND (INFO not like '%INTERNAL%' or INFO is null) -- ★内部SQLを表示しない
AND OPERATION_CODE in (1,2,3,5,6,7,36) -- ★DDL、DML、START、COMMITとROLLBACKのみ取得
)
WHERE
OPE_CODE_MIN <> 6
AND (OPE_CODE_MIN <> 5 or OPERATION_CODE = 5)
ORDER BY COMMIT_SCN,xid,SEQUENCE#;