Oracle 9i started introducing Flashback technology into the Oracle RDBMS. Flashback unlocks the UNDO data already available in the Oracle database for read-consistency, for other applications. Using Flashback queries for example, we can take a similar look in the past that Oracle takes for executing long-running queries or supporting the transaction isolation level of Serializable. Using Flashback, we can compare data as it is right now with how it was several transactions or time units ago.
Over the course of the last six years or so, Flashback has broadened considerably. There is the Flashback Query, the Flashback Table including the ‘undrop table’, Flashback Database and as of 11g we also have Flashback Archive, for more fine-grained control over which tables should be flashback-able for a set period of time.
This article introduces the latest refinement in the Flashback functionality: the partial table flashback, now possible with Oracle 11g.
Take the following scenario:
your staff has been working hard on processing dozens of orders from your customers. And when everyone feels the work is done, the atmosphere is celebratory, the DBA intern comes along and accidentally deletes a bunch of records. Before Flashback, that would have stopped the party right there and then. However, with Flashback, it is an easy action for the DBA to compensate for the intern’s mishap: FLASHBACK TABLE TO SYSTIMESTAMP – 1/24/6 to return to the situation as it was 10 minutes ago, just before the records were accidentally destroyed. In less than a minute, the party is back on.
Now this scenario: the same situation – with a tiny difference: the intern started his day early, made his colossal error right away which was only discovered at the end of the day. Between the error – destruction of dozens of customer records – and the time of discovery, many other records in the same table have been added and modified and some have been purposefully removed. Using FLASHBACK table, as before, we could return to the situation of this morning, before the intern came along. However, that would undo the work of all our staff on that table’s data during the rest of the day. Alternatively, using Flashback Query, we can reconstruct data record by record. A painstaking manual process, but it can be done without loss of our staff’s efforts.
Partial Table Flashback helps us out in this second scenario: we can perform a Flashback on the table, but only for selected records! All we need to do is construct a Filter Condition that will include the records that should be flashed back or exclude those that should not. DBaMiS_PARTIAL_FLASHBACK will take care of the rest, without manual intervention and record-by-record recovery.
Other scenarios that are facilitated by the Partial Table Flashback:
- we have run an import and merge of sales data of one of a dozen departments. Since that import, we have made hundreds of useful changes in various records. We have only now found out that the import was done using an old file – and a new one is not yet available. We want to flashback all sales data for the one department that we did the erroneous import for
- our HRM departments has been updating the Salary and Compensation plan data for the employees – two columns out of 25 columns in our STAFF_MEMBERS table. That was last week. However, due to union intervention, we need to start negotiations, and until those are done, the old Salary and Bonus values should be reinstated. Unfortunately, many other changes have been made to the Staff Members data since last week. We want to partially flashback just the values of those two columns
- a vindictive (now former) employee has been randomly (or so it seems) deleting customer records from our CRM system. We would like to get those records back! However, other changes in the table – inserts of new customers and updates to other records – should not be lost. A partial flashback that only restores deleted records is requested!
This and more can be done with Partial Table Flashback in Oracle 11g!
Let’s look at a step by step example:
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
At this starting point, we go off to make some changes:
SQL> update emp
2 set ename = initcap(ename)
3 /
14 rows updated.
SQL> commit
2 /
Commit complete.
SQL> Delete from emp
2 where deptno =10
3 /
3 rows deleted.
SQL> update emp
2 set sal = sal + 100
3 where job ='CLERK'
4 /
3 rows updated.
SQL>
SQL>
SQL> commit
2 /
Commit complete.
SQL> update emp
2 set job = 'BOSS'
3 where job='MANAGER'
4 /
2 rows updated.
SQL>
SQL> commit
2 /
Commit complete.
SQL> insert into emp
2 (empno, ename, job, sal, deptno)
3 values
4 (1331, 'LEX', 'PUPIL', 0.5, 20)
5 /
1 row created.
SQL>
SQL> commit
2 /
Commit complete.
SQL>
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 Smith CLERK 7902 17-DEC-80 1300 20
7499 Allen SALESMAN 7698 20-FEB-81 1600 300 30
7521 Ward SALESMAN 7698 22-FEB-81 1250 500 30
7566 Jones BOSS 7839 02-APR-81 2975 20
7654 Martin SALESMAN 7698 28-SEP-81 1250 1400 30
7698 Blake BOSS 7839 01-MAY-81 2850 30
7788 Scott ANALYST 7566 19-APR-87 3000 20
7844 Turner SALESMAN 7698 08-SEP-81 1500 0 30
7876 Adams CLERK 7788 23-MAY-87 1600 20
7900 James CLERK 7698 03-DEC-81 1450 30
7902 Ford ANALYST 7566 03-DEC-81 3000 20
1331 LEX PUPIL .5 20
12 rows selected.
At this point we realize our mistake: we should never have removed the Employees from Department 10! Now we need the partial table flashback for the lost records in department 10, without losing the changes for the CLERKs and the BOSS.
SQL> begin
2 dbamis_partial_flashback.partial_flashback_table
3 ( p_table_name => 'EMP'
4 , p_schema_name => 'SCOTT'
5 , p_to_timestamp => SYSTIMESTAMP - 1/2 -- 12 hours ago
6 , p_to_scn => null
7 , p_filter_condition => 'deptno = 10 '
8 );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 Smith CLERK 7902 17-DEC-80 1300 20
7499 Allen SALESMAN 7698 20-FEB-81 1600 300 30
7521 Ward SALESMAN 7698 22-FEB-81 1250 500 30
7566 Jones BOSS 7839 02-APR-81 2975 20
7654 Martin SALESMAN 7698 28-SEP-81 1250 1400 30
7698 Blake BOSS 7839 01-MAY-81 2850 30
7788 Scott ANALYST 7566 19-APR-87 3000 20
7844 Turner SALESMAN 7698 08-SEP-81 1500 0 30
7876 Adams CLERK 7788 23-MAY-87 1600 20
7900 James CLERK 7698 03-DEC-81 1450 30
7902 Ford ANALYST 7566 03-DEC-81 3000 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.
Lucky us: all employees in Department 10 restored, and all other changes retained – initcap ename, BOSS instead of Manager, New Employee Lex and the slightly higher salary for the CLERKs – except for the Clerks in Department 10.
Another example:
SQL> prompt back to the beginning
back to the beginning
SQL>
SQL> begin
2 dbamis_partial_flashback.partial_flashback_table
3 ( p_table_name => 'EMP'
4 , p_schema_name => 'SCOTT'
5 , p_to_timestamp => SYSTIMESTAMP - 1/24*1.25
6 , p_to_scn => null
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Make a number of changes to SAL and COMM, JOB and HIREDATE:
SQL> update emp
2 set sal = 1
3 , comm = 1000
4 /
14 rows updated.
SQL> update emp
2 set hiredate = sysdate-1000
3 where ename='ALLEN'
4 /
1 row updated.
SQL> update emp
2 set job = lower(job)
3 /
14 rows updated.
SQL> insert into emp
2 (empno, ename, hiredate, sal, deptno)
3 values
4 ( 1313, 'TOBIAS', sysdate, 1000, 20)
5 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH clerk 7902 17-DEC-80 1 1000 20
7499 ALLEN salesman 7698 06-JAN-05 1 1000 30
7521 WARD salesman 7698 22-FEB-81 1 1000 30
7566 JONES manager 7839 02-APR-81 1 1000 20
7654 MARTIN salesman 7698 28-SEP-81 1 1000 30
7698 BLAKE manager 7839 01-MAY-81 1 1000 30
7788 SCOTT analyst 7566 19-APR-87 1 1000 20
7844 TURNER salesman 7698 08-SEP-81 1 1000 30
7876 ADAMS clerk 7788 23-MAY-87 1 1000 20
7900 JAMES clerk 7698 03-DEC-81 1 1000 30
7902 FORD analyst 7566 03-DEC-81 1 1000 20
1313 TOBIAS 03-OCT-07 1000 20
7782 CLARK manager 7839 09-JUN-81 1 1000 10
7839 KING president 17-NOV-81 1 1000 10
7934 MILLER clerk 7782 23-JAN-82 1 1000 10
15 rows selected.
Now we need partial table flashback: to reset the values in SAL and COMM to their initial values.
SQL> begin
2 dbamis_partial_flashback.partial_flashback_table
3 ( p_table_name => 'EMP'
4 , p_schema_name => 'SCOTT'
5 , p_to_timestamp => SYSTIMESTAMP - 1/24*1.25 -- 15 minutes ago
6 , p_to_scn => null
7 , p_column_list => 'EMPNO,SAL, COMM ' -- only undo updates on SAL and COMM
8 , p_undo_insert_flag => false -- do not undo inserts of new records
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH clerk 7902 17-DEC-80 800 20
7499 ALLEN salesman 7698 06-JAN-05 1600 300 30
7521 WARD salesman 7698 22-FEB-81 1250 500 30
7566 JONES manager 7839 02-APR-81 2975 20
7654 MARTIN salesman 7698 28-SEP-81 1250 1400 30
7698 BLAKE manager 7839 01-MAY-81 2850 30
7788 SCOTT analyst 7566 19-APR-87 3000 20
7844 TURNER salesman 7698 08-SEP-81 1500 0 30
7876 ADAMS clerk 7788 23-MAY-87 1100 20
7900 JAMES clerk 7698 03-DEC-81 950 30
7902 FORD analyst 7566 03-DEC-81 3000 20
7782 CLARK manager 7839 09-JUN-81 2450 10
7839 KING president 17-NOV-81 5000 10
7934 MILLER clerk 7782 23-JAN-82 1300 10
1313 TOBIAS 03-OCT-07 1000 20
15 rows selected.
And there we go: the SAL and COMM values are restored, while the changed in JOB and ALLEN’s HIREDATE as well as the new Employee TOBIAS are retained: it’s a partial table flashback!
Note: the package we use here, dbamis_partial_flashback, is not a standard supplied package, it is a package I created myself. However, it is fairly simple – less than 200 lines of code – and uses standard functionality: primarily dynamic SQL, dbms_comparison and flashback query. In a subsequent article, I will discuss the package in detail and make it available for download.
