Oracle flashback transaction query example 1

create user trx_1 identified by trx_1
default tablespace   users
temporary tablespace temp
quota unlimited on   users;

create user trx_2 identified by trx_2
default tablespace   users
temporary tablespace temp
quota unlimited on   users;

create user trx_admin identified by trx_admin
default tablespace   users
temporary tablespace temp
quota unlimited on   users;

grant create session to trx_admin, trx_1, trx_2;
      
grant create table, create public synonym, select any transaction to trx_admin;

grant select on v_$database to trx_admin;

conn trx_admin/trx_admin

create table trx_table (
  id  number,
  tx  varchar2(10)
);

grant all on trx_table to trx_1, trx_2;

create public synonym trx_table for trx_table;

insert into trx_table values (1, 'one'  ); 
insert into trx_table values (2, 'two'  ); 
insert into trx_table values (3, 'three'); 
insert into trx_table values (4, 'four' ); 
insert into trx_table values (5, 'five' ); 

commit;

create table after_init_load as select current_scn scn from sys.v_$database;

conn trx_1/trx_1

delete from trx_table where id = 1;
update trx_table set tx = 'TWO'   where id = 2;
update trx_table set tx = 'THREE' where id = 3;

insert into trx_table values (6, 'SIX');

commit;

conn trx_2/trx_2

update trx_table set tx = 'Three' where id = 3;
update trx_table set tx = 'FOUR'  where id = 4;

delete from trx_table where id = 5;

insert into trx_table values (7, 'SEVEN');

commit;

conn trx_admin/trx_admin

select * from trx_table order by id;

        ID TX
---------- ----------
         2 TWO
         3 Three
         4 FOUR
         6 SIX
         7 SEVEN

--set serveroutput on size 1000000 format wrapped
begin

  for r in (

    select /*undo_change#,*/ undo_sql 
      from flashback_transaction_query 
     where table_name  = 'TRX_TABLE' and 
           table_owner = 'TRX_ADMIN' and
           logon_user  = 'TRX_1'     and
           commit_scn  > (select scn from after_init_load)
     order by undo_change#
  ) loop

--  dbms_output.put_line('r.undo_change#: ' || r.undo_change#);
--  dbms_output.put_line('  ' || r.undo_sql);

    execute immediate 
    substr(r.undo_sql, 1, length(r.undo_sql)-1); --remove trailing ';'

  end loop;

end;
/

select * from trx_table order by id;

        ID TX
---------- ----------
         1 one
         2 two
         3 three
         4 FOUR
         7 SEVEN