Google+ Followers

Pages

Thursday, September 23, 2010

Flashback Query

Flashback query:

Query all data as it existed at a specific point in time.
Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).


Example  ---          Delete Case


SQL> --- At 11 : 34
SQL> DELETE FROM EMP
            WHERE JOB='SALESMAN';
4 rows deleted.

SQL> COMMIT;
Commit complete.

Next, use the SELECT . . . AS OF query to retrieve Flashback data from the past.
SQL> --- At 12 : 19
SQL>  SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-1/24;
Or
SQL>  SELECT * FROM emp AS OF TIMESTAMP
             TO_TIMESTAMP ( '2010-03-05 11:33:30' , 'YYYY-MM-DD HH24:MI:SS');

Once we confirm the validity of the accidentally deleted data, it’s easy to reinsert the data by using the previous query as part of an INSERT statement, as shown here:

SQL>INSERT INTO EMP SELECT * FROM EMP
           AS OF TIMESTAMP SYSDATE-1/24
           WHERE JOB='SALESMAN'
4 rows created.

SQL> COMMIT ;
Commit complete.

SQL>  SELECT * FROM EMP


UPDATE CASE

SQL> --- AT 12 : 22
SQL> SELECT * EMP  WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81         950                     30

SQL> UPDATE EMP SET SAL=3000
            WHERE EMPNO=7900;
1 rows updated.

SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP
           WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81       3000                     30

SQL> --- AT 12 : 40
SQL> SELECT * FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
             WHERE EMPNO=7900
7900   JAMES       CLERK           7698   03-DEC-81         950                     30

SQL>  UPDATE EMP SET SAL=(SELECT SAL FROM EMP AS OF TIMESTAMP
           TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
           WHERE EMPNO=7900)
           WHERE EMPNO=7900;
1 row updated.

SQL> SELECT * FROM EMP WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81         950                     30



UPDATE CASE 2


SQL> --- AT 12 : 22

SQL> SELECT * EMP  WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

SQL> UPDATE EMP SET SAL=10000
            WHERE JOB=’SALESMAN’;
4 rows updated.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM EMP
           WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       10000          300          30
7521   WARD         SALESMAN        7698   22-FEB-81       10000          500          30
7654   MARTIN      SALESMAN        7698   28-SEP-81       10000       1400          30
7844   TURNER     SALESMAN        7698   08-SEP-81       10000              0          30

SQL> --- AT 12 : 40

SQL> SELECT * FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
             WHERE JOB=’SALESMAN’
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

SQL> UPDATE EMP E
            SET SAL=( SELECT SAL FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP('2010-03-08 15:25:04','RRRR-MM-DD HH24:MI:SS')
            WHERE E.EMPNO=EMPNO AND JOB='SALESMAN')
            WHERE JOB='SALESMAN';
4 row updated.
SQL> SELECT * FROM EMP WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

The previous examples use a time stamp to pinpoint the exact time the data was accidentally dropped. We could use the SCN for the transaction instead of time stamps. If you need to be very specific regarding the time point, use the time-stamp method to specify the time.

No comments:

Post a Comment