Feb 11, 2009

Flashback Query

Flashback Query in 9i
---------------

/*In 9i flashback is for User level (ie : Schema level)*/

CONN SYS
GRANT EXECUTE ON dbms_flashback TO user;

CONN TO USER

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('31-JAN-2009:115427','DD-MON-YYYY:HH24MISS'))

SELECT * FROM ......

1)WITHOUT DISABLING DBMS_FLASHBACK WE CANT DO ANY TRANSACTIONS
2)U CANNOT GET DATA FROM A TRUNCATED TABLE

EXEC DBMS_FLASHBACK.DISABLE; --After this only DML transactions can be done

--------------------------------------------------------------------------------
Flashback Query in 10g
----------------

/*In 10g flashback is for Table level */

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

FLASHBACK TABLE scott.emp
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);

No comments: