This article will be discussing the Flashback Query feature of Oracle database. As the name suggests, this feature is about querying database at a previous point-in-time. We can provide a previous time while executing a query, and the database will show results that would have shown at that time.

Let’s say HR updates salaries of all employees at 10:00 am. Later on, it is discovered that there was a problem with the increment formula, and the salaries were not updated as expected. Now HR wants to restore just the salary column without disturbing other data in table.

This can be done using Oracle Flashback Query. We will query salary information at 09:59 am, create a temporary table out of it, and then update main table with this salary information.

First we need to enable Flashback feature. We need to take care of following prerequisites to use this feature. This article will use ORCL as CDB and PDBORCL as PDB, but following code will work perfectly on non-container database.

Prerequisites

Database should be in archivelog mode
Database should have flashback on

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  947847168 bytes                                       
Fixed Size                  2295176 bytes                                       
Variable Size             603982456 bytes                                       
Database Buffers          335544320 bytes                                       
Redo Buffers                6025216 bytes                                       
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Example
Let’s create our table and insert some data in it:

SQL> CONN SYS@PDBORCL AS SYSDBA
Connected.

SQL> ALTER PLUGGABLE DATABASE PDBORCL OPEN;

Pluggable database altered.

SQL> CREATE USER USER1 IDENTIFIED BY USER1 CONTAINER=CURRENT
  2  DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

User created.

SQL> GRANT CONNECT, RESOURCE TO USER1;

Grant succeeded.

SQL> CONN USER1/USER1@PDBORCL
Connected.
SQL> CREATE TABLE EMP (
  2    EMPNO     NUMBER,
  3    ENAME     VARCHAR2(10),
  4    SALDATE   DATE,
  5    SAL       NUMBER
  6  );

Table created.

SQL> 
SQL> INSERT INTO EMP (EMPNO, ENAME, SALDATE, SAL)
  2  VALUES (10, 'JOHN', TO_DATE('01-01-2012', 'DD-MM-YYYY'), 5000);

1 row created.

SQL> 
SQL> INSERT INTO EMP (EMPNO, ENAME, SALDATE, SAL)
  2  VALUES (11, 'MARK', TO_DATE('17-03-2012', 'DD-MM-YYYY'), 4500);

1 row created.

SQL> 
SQL> INSERT INTO EMP (EMPNO, ENAME, SALDATE, SAL)
  2  VALUES (12, 'KING', TO_DATE('11-02-2012', 'DD-MM-YYYY'), 7000);

1 row created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> SELECT EMPNO, ENAME, SAL
  2    FROM EMP;

EMPNO ENAME        SAL                                                           
----- ----- ----------                                                      
   10 JOHN        5000                                                          
   11 MARK        4500
   12 KING        7000

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') CURR_TIME
  2  FROM DUAL;

CURR_TIM                                                                        
--------                                                                        
09:59:00

Now let’s give all employee a raise with an erroneous formula:

SQL> UPDATE EMP
  2  SET SAL=SAL + (SAL/100*EMPNO);

3 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') CURR_TIME
  2  FROM DUAL;

CURR_TIM                                                                        
--------                                                                        
10:00:03    

SQL> SELECT EMPNO, ENAME, SAL
  2    FROM EMP;

EMPNO ENAME        SAL                                                           
----- ----- ----------                                                      
   10 JOHN        5500                                                          
   11 MARK        4495
   12 KING        7840

Now we want to recover salaries that were at 09:59:00. Let’s use Flashback query to retrieve it. Flashback queries use the “AS OF TIMESTAMP” clause to specify time and query data that would have show at that time.

SQL> CREATE TABLE TEMP_EMP AS
  2    SELECT EMPNO, SAL
  3      FROM EMP AS OF TIMESTAMP
  4      TO_TIMESTAMP('02-01-2014 09:59:00', 'DD-MM-YYYY HH24:MI:SS'); 

Table created.

SQL> SELECT * FROM TEMP_EMP;

EMPNO        SAL
----- ----------
   10       5000
   11       4500
   12       7000

So we have our salaries back, and the only thing left is to update them in our main EMP table. Let’s update EMP table

SQL> UPDATE EMP E
  2    SET E.SAL = (SELECT SAL FROM TEMP_EMP WHERE EMPNO=E.EMPNO);

3 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP;

EMPNO ENAME        SAL
----- ----- ----------
   10 JOHN        5000
   11 MARK        4500
   12 KING        7000

Now we have our salaries back as they were at 09:59:00 by using the Oracle Flashback query feature. But we have limit on going back in time. The database will only retain flashback logs for minutes specified in db_flashback_retention_target initialization parameter, and it can only go as back as it has flashback logs. By default it is 1440 minutes, which is 1 day. So you need to update this parameter if you want to go further back in time.

Link to script that contains the examples in this post.

Comment

2014-02 – Oracle Flashback Query

HEADQUARTERS