What you need to know about Oracle Flashback query



Oracle has introduced Flashback Technology  that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:

  • Perform queries that return past data
  • Perform queries that return metadata that shows a detailed history of changes to the database
  • Recover tables or rows to a previous point in time
  • Automatically track and archive transactional data changes
  • Roll back a transaction and its dependent transactions while the database remains online

Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions.

Undo data is persistent and survives a database shutdown. By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform the following actions:

  • Roll back active transactions
  • Recover terminated transactions by using database or process recovery
  • Provide read consistency for SQL queries

Here in this articles , we will be discussing Oracle flashback query feature which is very useful . Some of the use cases are

  1. Recovering lost data or undoing incorrect, committed changes.For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
  2. Comparing current data with the corresponding data at some time in the past.

    For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.

  3. Checking the state of transnational data at a particular time.

    For example, you can verify the account balance of a certain day.

What is Oracle  Flashback query

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the as of clause. So if you have deleted certain rows and committed them, you can still recover them using oracle flashback query provided undo tablespace is big enough and undo data is still present

Syntax to Use Oracle Flashback  query

select * from <table-name>   AS OF TIMESTAMP TO_TIMESTAMP(‘<date>’,<timestamp format>);

Examples

SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP(‘2017-08-29 11:34:12’, ‘YYYY-MM-DD HH24:MI:SS’);

Or

We can set the timestamp  format for the session

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ‘DD-MON-YYYY HH24:MI:SS.FF3′;

SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP(’29-AUG-2017 11:34:12.000’);

or

select * from emp as of timestamp sysdate-1/24;

Important points

  1. We can insert this flashback data in the table also

create table emplyees_bk as SELECT *
FROM employes AS OF TIMESTAMP TO_TIMESTAMP(’29-AUG-2017 11:34:12.000′);

2) We can use with where condition also

SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP(‘2005-04-04 09:30:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE name = ‘JOHN’;

3) You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.

CREATE VIEW hour_ago AS
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’60’ MINUTE);


Leave a Reply