Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, August 14, 2015

Oracle Flashback versions query/ Flashback query

Flashback Query:


To configure your database for the Oracle Flashback Transaction Query feature, make sure:
■ The Oracle Database is running with version 10.0 or higher compatibility.
■ Enable supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

If not done, enable minimal and primary key supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;


■ If you want to track foreign key dependencies, enable foreign key supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;


SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUP_MIN_LOG     SUP_PK            SUP_ALL
--------             ---                ---
YES                NO                  NO


SQL> select location_id,postal_code from hr.locations;

LOCATION_ID POSTAL_CODE
----------- ------------
      1000 00989
      1100 10934
      1200 1689
      1300 6823
      1400 26192
      1500 99236
      1600 50090
      1700 98199
      1800 M5V 2L7
      1900 YSW 9T2
      2000 190518
      2100 490231
      2200 2901
      2300 540198
      2400            ←- Null postal_code here
      2500 OX9 9ZB
      2600 09629850293
      2700 80925
      2800 01307-002
      2900 1730
      3000 3095
      3100 3029SK
      3200 11932

23 rows selected.




SQL> select name,open_mode,flashback_on from v$database;

NAME      OPEN_MODE           FLASHBACK_ON
--------- -------------------- ------------------
ORCL      READ WRITE           YES


SQL> show user
USER is "HR"

Update the postal_code for location_id -2400 to some random junk.

SQL> update locations set postal_code ='FLASHQRY' where location_id=2400;

1 row updated.

SQL> commit;

Commit complete.


SQL> select location_id,postal_code from locations where location_id=2400;

LOCATION_ID POSTAL_CODE
----------- ------------
      2400 FLASHQRY


SQL> show parameter flashback

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target         integer     1440

SQL> select 1440/60 in_hours from dual;

 IN_HOURS
----------
   24

To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.


SQL> !date;            
Fri Aug 14 06:14:12 EDT 2015

Look up the data changes:

SQL>  select location_id,postal_code from locations as of timestamp
 2  to_timestamp('2015-08-14 06:00:00','yyyy-mm-dd hh:mi:ss') where location_id=2400;

LOCATION_ID POSTAL_CODE
----------- ------------
      2400 FLASHQRY


SQL> select location_id,postal_code from locations as of timestamp
 2  to_timestamp('2015-08-14 05:00:00','yyyy-mm-dd hh:mi:ss') where location_id=2400;

LOCATION_ID POSTAL_CODE
----------- ------------
      2400

Rollback the row data as of 08/14/2015 5:00 am

SQL> update locations set postal_code=(select postal_code from locations as of timestamp
     to_timestamp('2015-08-14 05:00:00','yyyy-mm-dd hh:mi:ss') where location_id=2400)
      where location_id=2400;

1 row updated.

SQL> commit;

Commit complete.

Verify the flashback versions query:

SQL> select location_id,postal_code from locations where location_id=2400;

LOCATION_ID POSTAL_CODE
----------- ------------
      2400

No comments: