我以前乱写的。
WITH a
AS (SELECT seg_owner,
seg_name,
rbasqn,
rbablk,
rbabyte,
rbablk * 512 + rbabyte curpos,
rs_id,
scn,
operation
FROM v$logmnr_contents
ORDER BY rs_id,decode(operation,'START',' ','COMMIT','ZZ')),
b AS (SELECT ROWNUM r, a.* FROM a),
c
AS (SELECT r,
seg_owner,
seg_name,
rs_id,
scn,
rbasqn,
rbablk,
rbabyte,
curpos,
LEAD (curpos, 1, 0) OVER (ORDER BY r) nextpos,
LEAD (rbasqn, 1, 0) OVER (ORDER BY r) next_rbasqn,
LEAD (rbablk, 1, 0) OVER (ORDER BY r) next_rbablk,
LEAD (rbabyte, 1, 0) OVER (ORDER BY r) next_rbabyte,
operation
FROM b),
d
AS (SELECT r,
seg_owner,
seg_name,
rs_id,
scn,
rbasqn,
rbablk,
rbabyte,
curpos,
nextpos,
nextpos - curpos redo_size,
next_rbasqn,
next_rbablk,
next_rbabyte,
operation
FROM c
WHERE nextpos0 and next_rbasqn = rbasqn)
SELECT * from d --where operation='COMMIT' and redo_size>=1024
也许根本不对。。你自己看看把 |
