HOME

Flashback techniques using UNDO

Contact Me

This section shows ways use Flashback techniques to query past data using UNDO.

Flashback Query TOP

Here is our initial data.


fbq1.jpg

Now let's delete a record

delete from EMPLOYEES where first_name='David' and last_name='Lee';

Querying again will show only 2 records.


fbq2.jpg

Now using flashback query feature.


fbq3.jpg

Using this flashback query feature, we can now re-insert the deleted data.
Re-querying shows that deleted data is back now.


fbq4.jpg

Same functionality can be achieved through DBMS_FLASHBACK package also.
Its useful when the existing PL/SQL code cannot be touched.

Current number of such employees is 3 as can be seen:

	select count(*) from EMPLOYEES where first_name='David' ;
	COUNT(*)
	--------
	3	

Now let us set flashback to the time when the employee was in deleted state.

Re-query shows the count as 2.

	execute  DBMS_FLASHBACK.ENABLE_AT_TIME (to_timestamp ('2017-04-11 12.55.00','YYYY-MM-DD HH.MI.SS')); 
		
	select count(*) from EMPLOYEES where first_name='David' ;
	COUNT(*)
	--------
	2	

Querying data shows 2 records:


fbq5.1.jpg

Disabling the flashback brings us back to present:

	execute  DBMS_FLASHBACK.DISABLE;

	select count(*) from EMPLOYEES where first_name='David' ;
	COUNT(*)
	--------
	3	

Flashback Versions Query TOP

Flashback versions query is used to see the past versions of a table row, between either two points in time or two SCNs, for the purpose of audit to know what all changes happened for the given row OR to undo the incorrect data changes.
With each COMMIT, a new version is created. If a row ins inserted and then updated and then deleted, the table will NOT contain that row but via flashback versions query we can know what all happened to that row in the past beween two time intervals or SCNs.

Some pseudo-columns involved in this operation are:

1) VERSIONS_STARTSCN and VERSIONS_STARTTIME: These pseudo-columns tell you the SCN and time stamp when this particular row was first created. If the VERSIONS_STARTTIME is null, the row was created before the lower time boundary of the query.
2) VERSIONS_ENDSCN and VERSIONS_ENDTIME: These pseudo-columns tell you when this particular row expired. If the VERSIONS_ENDTIME column value is null, it means that the row is either current or that it has been deleted.
3) VERSIONS_OPERATION: Tells the type of DML activity that was performed on the particular row. The column has three possible values: I - insert, D - delete, and U - update operation.
4) VERSIONS_XID: This pseudo-column displays the unique transaction identifier of the transaction that resulted in this row version.

Lets see an example to understand it better:

	Insert a new row in EMPLOYEES table:
	
	Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
	values (99,'Mike','Salve','MSalve','515.123.1111',to_date('19-JUN-03','DD-MON-RR'),'AD_VP',24500,null,null,90);
	commit;
	
	select employee_id,first_name,last_name,salary  from employees where employee_id=99;
	
	EMPLOYEE_ID FIRST_NAME      LAST_NAME       SALARY
	----------- --------------- --------------- ----------
         99     Mike            Salve           24500
		 
	Get Current SCN
	select dbms_flashback.get_system_change_number n from dual;
	n
	-----------------
	2382713	
	
	We can see that only 1 version exists as of now, created via Insert operation:
	
	SELECT versions_xid AS XID, versions_startscn AS START_SCN,
	versions_endscn AS END_SCN,
	versions_operation AS OPERATION,
	employee_id,first_name,last_name,salary  FROM EMPLOYEES
	VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
	AS OF SCN 2382713
	WHERE employee_id = 99;
	
        XID              START_SCN    END_SCN 	OP 	EMPLOYEE_ID FIRST_NAME	LAST_NAME    SALARY
	--------------------------------------------------------------------------------------------
    050008007C080000     2382649      2382759    I    99          Mike        Salve        24500
	
	Lets update Salary and then delete the record.
	
	update employees set salary = 100 where employee_id=99;
	commit;
	delete from employees where employee_id=99;
	commit;
	
	Get Current SCN
	select dbms_flashback.get_system_change_number n from dual;
	n
	-----------------
	2382771	
	
	Using versions query again:
	
	SELECT versions_xid AS XID, versions_startscn AS START_SCN,
	versions_endscn AS END_SCN,
	versions_operation AS OPERATION,
	employee_id,first_name,last_name,salary  FROM EMPLOYEES
	VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
	AS OF SCN 2382771
	WHERE employee_id = 99;

       XID              START_SCN    END_SCN 	OP 	EMPLOYEE_ID FIRST_NAME	LAST_NAME   SALARY
	---------------------------------------------------------------------------------------------
	07001B0076060000    2382767                 D	99          Mike        Salve       100
	0B00090029000000    2382759    2382767      U	99          Mike        Salve       100
	050008007C080000    2382649    2382759      I   99          Mike        Salve       24500

	This shows that the row was updated with salary as 100 and then it was deleted.
	
	So we now know what all operations were done with employee id 99.
	END_SCN being null in topmost row means that the row is deleted (or current).
	
		

Flashback Transaction Query TOP

In-progress............
Thanks...

TOP