Flashback Query - Features,Properties - Oracle Tablespaces

Q.  Which two statements about Flashback Query are true?
- Published on 12 Aug 15

a. It helps in row-level recovery from user errors
b. It can be performed to recover ALTER TABLE statements
c. It fails when undo data pertaining to the transaction is overwritten
d. BOTH B & C
e. BOTH A & C

ANSWER: BOTH A & C
 

    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


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)