Learn It Yourself: Oracle Flashback Features

The script below is the one I had used to learn more about Oracle Flashback features and then to teach development teams how to use flashabck features. I have embeded some comments within the script to explain few points. Just copy and run the script to get better understanding of Oracle Flashback features. Enjoy!

REM *****************************************************************
REM Name: OraFlash.sql
REM Author: Jay Mehta
REM Description: A script to demonstrate Oracle flashback features
REM *****************************************************************
REM
REM **** drop Scripts
DROP TABLE TEST PURGE;
DROP SEQUENCE TEST_SEQ;
DROP RESTORE POINT before_insert ;
DROP RESTORE POINT before_upper_update ;
DROP RESTORE POINT before_lower_update ;
DROP RESTORE POINT before_delete ;
REM *****************************************************************
REM
REM *****************************************************************
ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY:HH24:MI:SS';
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
REM
REM **** Create a test table
REM
REM This table will be used in test cases
CREATE TABLE TEST
(   ID NUMBER(9),
    NAME VARCHAR2(30),
    TIME DATE ) ;
REM
REM **** You must enable row movement to perform FLASHBACK .. TO TIMESTAMP/SCN/RESTORE POINT
REM **** FLASHBACK ..... BEFORE DROP doesn't require Row Movement Enabled.
REN
ALTER TABLE TEST ENABLE ROW MOVEMENT;
REM To show that index and trigger are also flashed back by FLASHBACK ..... BEFORE DROP   
CREATE UNIQUE INDEX TEST_PK
ON TEST (ID);
ALTER TABLE TEST
ADD CONSTRAINT TEST_PK
PRIMARY KEY(ID);
CREATE SEQUENCE TEST_SEQ;
REM **** Create a trigger on Test Table
CREATE OR REPLACE TRIGGER TEST_BRI_TRG
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
     SELECT TEST_SEQ.NEXTVAL
     INTO :new.id
     FROM dual;
     IF SQL%notfound THEN
       raise_application_error(-20031,
           'The sequence generator IS NOT wo;king');
     END  IF;
  END IF;
END;
/

REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_insert ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
REM **** Insert Data
INSERT INTO TEST(ID,NAME,TIME)
VALUES(1,'Jay',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(2,'Jim',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(3,'John',SYSDATE);
COMMIT ;
SELECT * FROM TEST ;
REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_upper_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
UPDATE TEST
SET NAME = UPPER(NAME);
COMMIT;
SELECT * FROM TEST ;
REM
CREATE RESTORE POINT before_lower_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;

Prompt ******* Update to Lower Case
UPDATE TEST
SET NAME = LOWER(NAME);
COMMIT;
SELECT * FROM TEST ;

CREATE RESTORE POINT before_delete ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
Prompt ******* Delete Data
DELETE FROM TEST ;
COMMIT;
Prompt ***** Data in Test Table at this moment
SELECT *
FROM TEST ;
Prompt ************  Update Timestamp as appropriate
REM Flashback Querty Examples with TIMESTAMP clause
REM ***** Data in Test Table 2 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 2/84600) ;
Prompt ***** Data in Test Table 3 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 3/84600) ;
Prompt ***** Data in Test Table 4 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 4/84600) ;
Prompt ***** Data in Test Table 5 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 5/84600) ;
Prompt ***** Data in Test Table 6 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 6/84600) ;
Prompt ***** Data in Test Table 7 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 7/84600) ;
Prompt ***** Data in Test Table 1 hour ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 1/24) ;
SELECT *
FROM V$RESTORE_POINT ;
REM
REM Flashback Row History - Retrieve Version History
REM Update Timestamp appropeiately
REM
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME,
       DECODE( VERSIONS_OPERATION
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation" ,
             ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_XID;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP TO_DATE('09/08/2010:10:30:20','MM/DD/YYYY:HH24:MI:SS')  AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1/1440 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;

REM ***** Flashback Row History - Undo Mistakes
REM
-- Get the UNDO Statement to undelete row
SELECT start_timestamp, commit_timestamp, operation, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid in ( SELECT  hextoraw(VERSIONS_XID)
        FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE )
order by start_scn;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
TABLE_NAME='TEST'
ORDER BY START_SCN ;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
XID = '0009002F0001CB51'
-- XID = HEXTORAW('0009002F0001CB51')
ORDER BY START_SCN ;

REM Flashback Table - Undo Mistakes
REM

SELECT * FROM TEST ;
REM Flashback Before Delete
REM User SCN, TIMESTAMP or RESTORE POINT
REM
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:08', 'MM/DD/YYYY:HH24:MI:SS' )) ;
REM FLASHBACK TABLE TEST TO SCN 2342345234 ;
FLASHBACK TABLE TEST TO RESTORE POINT before_delete;
SELECT * FROM TEST ;

-- Flashback Before Last Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:05', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_upper_update;
SELECT * FROM TEST ;

-- Flashback Before First Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/09/2010:07:00:00', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_lower_update;
SELECT * FROM TEST ;
REM
REM Flashback Drop - Recover Dropped Tables
REM
DROP TABLE TEST;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;
FLASHBACK TABLE TEST TO BEFORE DROP  RENAME TO TEST1 ;
SELECT * FROM RECYCLEBIN ;
SELECT TABLE_NAME FROM USER_TABLES ORDER BY 1;
SELECT * FROM TEST;

-- permanent drop
DROP TABLE TEST PURGE ;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;