Flashback Transaction Query is used to retrieve metadata and historical data for single and all transactions in a time interval from a static data dictionary view FLASHBACK_TRANSACTION_QUERY.
The FLASHBACK_TRANSACTION_QUERY has a column UNDO_SQL which is the opposite of DML which caused the change. You can use the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to roll back transaction and dependent transactions. This procedure does not commit the rollback but temporarily holds the rollback until a manual commit is made. To perform the flashback transaction query the database must be in archivelog mode and supplemental logging must be enabled with command: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
For a user to use the flashback query the admin must grant following.
- Grant flashback to user.
- Grant select privileges on objects.
- To allow queries on all tables grant FLASHBACK ANY TABLE privilege.
- Grant SELECT ANY TRANSACTION privilege.
- Must have SELECT, UPDATE, DELETE, INSERT privilege on that table.
- Execute privilege on DBMS_FLASHBACK package.
This article details setting up flashback transactions for your database, granting user scott the appropriate privileges, and performing flashback.
- Logon to your SQLPLUS as sysdba.
[oracle@linux2 flash_recovery_area]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 08:25:35 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Verify that the database is in archivelog mode, if not switch to archive log mode.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2708
Next log sequence to archive 2710
Current log sequence 2710
SQL>
- Issue the command ‘ ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;’
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL>
- To allow queries on all tables grant FLASHBACK ANY TABLE privilege.
SQL> grant flashback any table to scott;
Grant succeeded.
SQL>
- Grant SELECT ANY TRANSACTION privilege.
SQL> grant select any transaction to scott;
Grant succeeded.
SQL>
- Execute privilege on DBMS_FLASHBACK package.
SQL> grant execute on dbms_flashback to scott;
Grant succeeded.
SQL>
- Logon as the user scott.
SQL> connect scott/password
Connected.
SQL>
- Create the test table fb_test.
create table fb_test(fname varchar2(30), amt number);
SQL> create table fb_test(fname varchar2(30), amt number);
Table created.
SQL>
- Insert single record into fb_test.
insert into scott.fb_test(fname,amt) values(‘Larry’,111);
commit;
SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);
commit;
1 row created.
SQL>
Commit complete.
SQL>
- Update single record and commit;
update scott.fb_test set amt=222 where fname=’Larry’;
commit;
SQL> update scott.fb_test set amt=222 where fname=’Larry’;
commit;
1 row updated.
SQL>
Commit complete.
SQL>
- Select value from scott.fb_test.
SQL> select * from scott.fb_test;
FNAME AMT
—————————— ———-
Larry 222
SQL>
- Query flashback_transaction_query for transactions against scott.fb_test.
SQL> select operation, undo_sql from flashback_transaction_query
where table_name=’FB_TEST’; 2
OPERATION
——————————–
UNDO_SQL
——————————————————————————–
INSERT
delete from “SCOTT”.”FB_TEST” where ROWID = ‘AAAXaKAAGAAALUdAAA’;
UPDATE
update “SCOTT”.”FB_TEST” set “AMT” = ‘111’ where ROWID = ‘AAAXaKAAGAAALUdAAA’;
SQL>
- You can now execute the UNDO_SQL column to rewind the update statement.
SQL> update “SCOTT”.”FB_TEST” set “AMT” = ‘111’ where ROWID = ‘AAAXaKAAGAAALUdAAA’;
1 row updated.
SQL> commit;
Commit complete.
SQL>
- For a more complex set of updates impacting muptiple objects, you would use the dbms_flashback.transaction_backout procedure.
- This completes flashback transaction query.
Larry Catt
OCP