set feedback off
set pages 5000
create table foo (
a number,
b varchar2(20)
);
insert into foo values (1,'one');
insert into foo values (2,'two');
insert into foo values (3,'three');
insert into foo values (4,'four');
insert into foo values (5,'five');
insert into foo values (6,'six');
insert into foo values (7,'seven');
insert into foo values (8,'eight');
insert into foo values (9,'nine');
commit;
create table foo_save as select rowid rowid_,foo.* from foo;
insert into foo values (10,'ten');
update foo set b = 'Nine' where a = 9;
delete from foo where a = 4;
update foo set b = 'Sixty six', a=66 where a=6;
delete from foo where a=2;
insert into foo values(20,'twenty');
update foo set b='Twenty' where a = 20;
select
max(case when c_ = 1 then s_ else 'upd' end) s_,
max(case when c_ = 1 then a else case when r_ = 1 then a else null end end) a,
max(case when c_ = 1 then b else case when r_ = 1 then b else null end end) b,
max(case when c_ = 1 then null else case when r_ = 2 then a else null end end) a_changed,
max(case when c_ = 1 then null else case when r_ = 2 then b else null end end) b_changed
from (
select row_number() over (partition by rowid_ order by s_ desc) r_, count(*) over (partition by rowid_) c_,rowid_, s_, a,b from (
select * from
(select 'del' s_, foo_save.* from foo_save minus select 'del' s_, rowid rowid_, foo.* from foo) union
select * from
(select 'add' s_,rowid rowid_, foo.* from foo minus select 'add' s_,foo_save.* from foo_save)
)
)
group by rowid_;
drop table foo;
drop table foo_save;