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.
- 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.
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.