Oracle 11g Partial Table Flashback (flashback only selected columns or a subset of records)

0

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.

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.