• Finding changed rows in a table

    FLASHBACK 22.09.2008 Comments Off

    select ename,sal, versions_xid,versions_startscn,versions_endscn
    from scott.emp versions
    between scn minvalue and maxvalue
    /

    SELECT UNDO_SQL
    FROM FLASHBACK_TRANSACTION_QUERY
    WHERE XID = ‘01000400D3110000′
    /

  • Enabling flashback

    FLASHBACK 22.09.2008 Comments Off

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter system set DB_FLASHBACK_RETENTION_TARGET=600; — time is in minutes (10 hours)
    alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
    alter system set DB_RECOVERY_FILE_DEST=’/recovery/flashback’;
    alter database flashback on;
    alter database open;

  • Recovering using flashback query

    FLASHBACK 22.09.2008 Comments Off

    select * from &&TABLE as of timestamp to_timestamp(’&&DD-&&MON-&&YY &HOUR:00:00′,’DD-MON-YY HH24: MI: SS’) minus select * from &&TABLE;
    – this will allow you to browse backward to find the time when the change occurred
    alter table &TABLE enable row movement;
    – row movement must be enabled for flashback queries
    flashback table &TABLE to timestamp to_timestamp(’&DD-&MON-&YY &HOUR:00:00′,’DD-MON-YY HH24:Mi:SS’);