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