Types Of Flashback Queries

Types Of Flashback Queries - Types Of Flashback Queries...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Types Of Flashback Queries Presented By Gaurav Zanzaria Flashback Query Flashback Query is querying data at a point in time. It lets you specify either a time or a system change number (SCN) and query using the committed data from the corresponding time. Types of flashback queries Flashback Queries can be classified into two types : Time Based Flashback Query. SCN based Flashback Query. For both types of queries, set the UNDO_RETENTION initialization parameter. Subprogram Description DISABLE Disables the Flashback mode for the entire session ENABLE_AT_SYSTEM_CH Enables Flashback for the ANGE_NUMBER entire session. ENABLE_AT_TIME Enables Flashback for the entire session. GET_SYSTEM_CHANGE_ NUMBER Returns the current SCN as an Oracle number. SCN_TO_TIMESTAMP Takes the current SCN and returns a TIMESTAMP. TIMESTAMP_TO_SCN Takes a TIMESTAMP as input and returns the current Time Based Flashback Query Time Based Flashback Query In time based flashback queries, we have to actually specify the date and time at which we need the data to be collected. To enable the flashback query mode, call Execute DBMS_FLASHBACK.ENABLE_AT_TIME Time Based Flashback Query Time Based Flashback Query ( contd) Time­Based Flashback Example 1: SQL> select systimestamp from dual; SYSTIMESTAMP ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ 23­JUL­03 02.24.01.000001 PM +05:30 SQL> select count(*) from emp; COUNT(*) ­­­­­­­­­­­­­­­­­ 14 Time Based Flashback Query Time Based Flashback Query ( contd) SQL> delete from emp; 14 rows deleted. SQL> commit; SQL> select count(*) from emp; COUNT(*) ­­­­­­­­­­ 0 Time­Based Flashback Example Time­Based Flashback Example ( Contd) SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('23­ JUL­03'); SQL> select count(*) from emp; COUNT(*) ­­­­­­­­­­ 14 SQL> EXECUTE DBMS_FLASHBACK.DISABLE; Time Based Flashback Query Time Based Flashback Query ( contd) EXAMPLE 2: SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003­04­04 09:30:00', 'YYYY­MM­DD HH:MI:SS') WHERE name = 'JOHN'; 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'); SCN­based Flashback Example SCN­based Flashback Example To know the current SCN Number SQL> commit; SQL> variable SCN_NO number; SQL> execute :SCN_NO :=DBMS_FLASHBACK.GET.SYSTEM_CHANG E_NUMBER; SCN­based Flashback Example SCN­based Flashback Example SQL> print SCN_NO; SCN_NO ­­­­­­­­­­ 419193 SQL> delete from dept; 5 rows deleted. SCN­based Flashback Example SCN­based Flashback Example SQL> commit; SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_ CHANGE_NUMBER(:SCN_NO); SQL> select count(*) from dept; COUNT(*) COUNT(*) ---------5 Advantages of SCN over time Advantages of SCN over time based flashback queries. Internally, flashback query results are always based on the SCN. Hence If you want precision when you flash back, you need to use the SCN. THANK YOU THANK YOU ...
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Ask a homework question - tutors are online