Flashback Transaction Backout

The dbms_flashback.transaction_backout procedure uses logminer data to backout transactions, so your data in that transaction was rolled back.
Requirements:
1. Database must be in Archive Mode

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50

2. Enable supplemental logging at Database level

SQL> alter database add supplemental log data;

Database altered.

Example:

SQL> delete from tb_test where object_id=11112;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
0

SQL> select versions_xid , object_id from tb_test versions between scn minvalue and maxvalue where object_id=11112;

VERSIONS_XID OBJECT_ID
---------------- ----------
08001600CC040000 11112

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5

Problem:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

Solve:
grant create any table to user_name;

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
1

Now... data was rolled back. We can review on...
- [DBA, USER]_FLASHBACK_TXN_STATE
- [DBA, USER]_FLASHBACK_TXN_REPORT

SQL> SELECT * FROM USER_FLASHBACK_TXN_STATE;

COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
09000B00D6040000 08001600CC040000 CASCADE

SQL> SELECT * FROM USER_FLASHBACK_TXN_REPORT;

read more