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.
DBA 
