Flashback Query - Features,Properties - Oracle Tablespaces

Discussion

• Nirja Shah   -Posted on 30 Sep 15
  Recovering at the Row Level
  - In a data recovery context, it is useful to be able to query the state of a table at a previous time.
  
  - If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.
  
  - Querying the past state of the table is achieved using the AS OF clause of the SELECT statement.
  
  - For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:
  
  SELECT * FROM EMPLOYEE AS OF TIMESTAMP
  TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE name = 'JOHN';
  
  - Restoring John's information to the table EMPLOYEE requires the following update:
  
  INSERT INTO employee
  (SELECT * FROM employee AS OF TIMESTAMP
  TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE name = 'JOHN');
  
  - The missing row is re-created with its previous contents, with minimal impact to the running database.

➨ Post your comment / Share knowledge