FLASHBACK FEATURES - Oracle database

FLASHBACK QUERY

1.      CREATE TABLE flashback_query_test (  id  NUMBER(10));

2.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

     722452 2014-09-04 13:34:12

3.      INSERT INTO flashback_query_test (id) VALUES (1);

4.      COMMIT;

5.      SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)

----------

         1

6.      SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2014-09-04 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

         0

7.      SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

  COUNT(*)

----------

         0

FLASHBACK VERSION QUERY1.      CREATE TABLE flashback_version_query_test (  id           NUMBER(10),

  description  VARCHAR2(50));

2.      INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

3.      COMMIT;

4.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

5.      UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

6.      COMMIT;

7.      UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

8.      COMMIT;

9.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

COLUMN versions_startscn FORMAT 99999999999999999

COLUMN versions_starttime FORMAT A24

COLUMN versions_endscn FORMAT 99999999999999999

COLUMN versions_endtime FORMAT A24

COLUMN versions_xid FORMAT A16

COLUMN versions_operation FORMAT A1

COLUMN description FORMAT A11

SET LINESIZE 200

10.  SELECT versions_startscn, versions_starttime,

       versions_endscn, versions_endtime,

       versions_xid, versions_operation,

       description

       FROM   flashback_version_query_test

       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2014-09-04 14:59:08', 'YYYY-MM-DD HH24:MI:SS')

       AND TO_TIMESTAMP('2014-09-04 14:59:36', 'YYYY-MM-DD HH24:MI:SS')

       WHERE  id = 1;

11.  SELECT versions_startscn, versions_starttime,

       versions_endscn, versions_endtime,

       versions_xid, versions_operation,

       description

       FROM   flashback_version_query_test

       VERSIONS BETWEEN SCN 725202 AND 725219

       WHERE  id = 1;

FLASHBACK TRANSACTION QUERY

1.      SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql

FROM   flashback_transaction_query

WHERE  xid = HEXTORAW('0600030021000000');

2.      update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA

AYAAA';

FLASHBACK TABLEFlashback table requires following privileges

1) FLASHBACK ANY TABLE or FLASHBACK object
2) SELECT,INSERT,DELETE and ALTER privs on table
3) Row movement must be enabled

CREATE TABLE flashback_table_test (

  id  NUMBER(10)

);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

     715315

INSERT INTO flashback_table_test (id) VALUES (1);

COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN

-----------

     715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)

----------

         0

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)

----------

         1

FLASHBACK DATABASE

• Database must be in archivelog mode and flashback should be enabled for performing this. When placed in flashback mode, we can observe flashback logs getting generated in flash_recovery_area

-- Create a dummy table.

CONN scott/tiger

CREATE TABLE flashback_database_test (

  id  NUMBER(10)

);

-- Flashback 5 minutes.

CONN sys/password AS SYSDBA

SHUTDOWN IMMEDIATE

STARTUP MOUNT EXCLUSIVE

FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);

ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.

CONN scott/tiger

DESC flashback_database_test

We can use following commands also in flashback database

FLASHBACK DATABASE TO TIMESTAMP my_date;

FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;

FLASHBACK DATABASE TO SCN my_scn;

FLASHBACK DATABASE TO BEFORE SCN my_scn;