Flashback versions query

This post show you Easy understanding about Flashback versions query and also How we can restore these data from flashback using EM DB console.

What Is Flashback Versions Query ?

You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.

You specify a Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. Here is the syntax:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where start and end are expressions representing the start and end of the time interval to be queried, respectively. The interval is closed at both ends: the upper and lower limits specified (start and end) are both included in the time interval.

 The flashback versions query feature, like Flashback Table, utilizes data from the undo segments, so you must configure your instance to use automatic undo management. The amount of data that you can retrieve with the VERSIONS_BETWEEN clause is limited by the setting of the UNDO_RETENTION initialization parameter. Because the UNDO_RETENTION parameter determines your row history, you should set this parameter based on your needed timeline to utilize flashback versions query. Also, you can use the RETENTION GUARANTEE clause to ensure that you will retain all critical undo data when using flashback versions query.

Now See Exmaples :

Step 1:

SQL> create user fbuser identified by fbuser;

User created.

SQL> grant connect,resource,dba to fbuser;

Grant succeeded.

SQL> conn fbuser/fbuser;
Connected.
SQL> create table flashtest as select * from hr.employees;

Table created.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
    4086851 2010-11-10 11:32:14

Step 2:

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
-------------------- -------------------------
Den                  DRAPHEAL
Alexander            AKHOO
Shelli               SBAIDA
Sigal                STOBIAS
Guy                  GHIMURO
Karen                KCOLMENA

6 rows selected.

Step 3:

SQL> update flashtest set email = EMAIL||'@HRDEPT' where department_id = 30;

6 rows updated.

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
-------------------- -------------------------
Den                  DRAPHEAL@HRDEPT
Alexander            AKHOO@HRDEPT
Shelli               SBAIDA@HRDEPT
Sigal                STOBIAS@HRDEPT
Guy                  GHIMURO@HRDEPT
Karen                KCOLMENA@HRDEPT

6 rows selected.

SQL> commit;

Commit complete.

SQL> delete flashtest where department_id = 30;

6 rows deleted.

SQL> commit;

Commit complete.

Step 4:

SQL> select first_name, email from flashtest where department_id = 30;

no rows selected

Step 5:

SQL> set lines 1000;
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation,
  2  first_name, email from flashtest VERSIONS BETWEEN SCN MINVALUE and MAXVALUE
  3  where department_id = 30;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V FIRST_NAME           EMAIL
----------------- --------------- ---------------- - -------------------- -------------------------
          4086869                 05000400DE0A0000 D Karen                KCOLMENA@HRDEPT
          4086869                 05000400DE0A0000 D Guy                  GHIMURO@HRDEPT
          4086869                 05000400DE0A0000 D Sigal                STOBIAS@HRDEPT
          4086869                 05000400DE0A0000 D Shelli               SBAIDA@HRDEPT
          4086869                 05000400DE0A0000 D Alexander            AKHOO@HRDEPT
          4086869                 05000400DE0A0000 D Den                  DRAPHEAL@HRDEPT
          4086866         4086869 0700080067080000 U Karen                KCOLMENA@HRDEPT
          4086866         4086869 0700080067080000 U Guy                  GHIMURO@HRDEPT
          4086866         4086869 0700080067080000 U Sigal                STOBIAS@HRDEPT
          4086866         4086869 0700080067080000 U Shelli               SBAIDA@HRDEPT
          4086866         4086869 0700080067080000 U Alexander            AKHOO@HRDEPT

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V FIRST_NAME           EMAIL
----------------- --------------- ---------------- - -------------------- -------------------------
          4086866         4086869 0700080067080000 U Den                  DRAPHEAL@HRDEPT
                          4086866                    Den                  DRAPHEAL
                          4086866                    Alexander            AKHOO
                          4086866                    Shelli               SBAIDA
                          4086866                    Sigal                STOBIAS
                          4086866                    Guy                  GHIMURO
                          4086866                    Karen                KCOLMENA

18 rows selected.

Okay. Now i want to restore this update row only using DBconsole and also How can we use Flashback versions query in EM Dbconsole.

Step 1:

Step 2:

Step 3:

Step 4:

Step 5

Step 6:

Step 7: You need to check Dependency Options.

After submitted, The particular SCN transactions restored.

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
——————– ————————-
Den                  DRAPHEAL@HRDEPT
Alexander            AKHOO@HRDEPT
Shelli               SBAIDA@HRDEPT
Sigal                STOBIAS@HRDEPT
Guy                  GHIMURO@HRDEPT
Karen                KCOLMENA@HRDEPT

6 rows selected.