ILM in 12c - Flashback Data Archive (importing history)

Importing flashback history from user-maintained history table

If the evolution of the data in a table is being tracked in user-defined tables by means of a user-defined mechanism – such as triggers, then its possible in 12c to import that history into a Oracle-provided flashback archive.

Using a flashback archive enabled table called emp, the following example shows how the history information may be imported from user-defined source.

Steps:

1. Starting off with a table called EMPLOYEES having employee number and salary.

SQL> select * from fdb_user.emp;

     EMPNO        SAL

---------- ----------

         1       1000

         2       2000

         3       3000

2. Assign a flashback archive for the the table.

SQL> alter table fdb_user.emp flashback archive fdba1;

Table altered.

3. Create the temp history table using dbms_flashback_archive.create_temp_history_table.

SQL> begin

 sys.dbms_flashback_archive.create_temp_history_table(

    owner_name1 => 'FDB_USER',

     table_name1 => 'EMP');

end;

  2    3    4    5    6  /

PL/SQL procedure successfully completed.

4. Populate the history table with a few trial records, specifying the scn, and dml operation.

Using INSERT statements, we create 2 update records for empno=1 in the temporary table.

insert into fdb_user.temp_history
(rid, startscn, endscn, xid, operation, empno, sal)
 values
 (
null,timestamp_to_scn(to_date('17-09-2014 14:30:00','dd-mm-yyyy hh24:mi:ss')),
 timestamp_to_scn(to_date('17-09-2014 14:30:00','dd-mm-yyyy hh24:mi:ss')),
 null,'U',1,800);

insert into fdb_user.temp_history
(rid, startscn, endscn, xid, operation, empno, sal
)
 values
 (
 null,timestamp_to_scn(to_date('17-09-2014 14:40:00','dd-mm-yyyy hh24:mi:ss')),
 timestamp_to_scn(to_date('17-09-2014 14:50:40','dd-mm-yyyy hh24:mi:ss')),
 null,'U',1,900);

Querying the data we created.

SQL> select * from fdb_user.temp_history;

RID   STARTSCN     ENDSCN XID              O      EMPNO        SAL

--- ---------- ---------- ---------------- - ---------- ----------

        560873     560873                  U          1        800

        561396     562013                  U          1        900

5. Import the history using dbms_flashback_archive.import_history.

This procedure call accepts 3 arguments

1.      Schema name of the table being tracked

2.      Name of the table being tracked.

3.      Name of the history table which contains the modification history.

  1 begin

  2   dbms_flashback_archive.import_history (

  3   owner_name1 => 'FDB_USER',

  4   table_name1 => 'EMP',

  5   temp_history_name => 'TEMP_HISTORY'

  6   );

  7* end;

SQL> /

PL/SQL procedure successfully completed.

6. Query the table with the flashback versions query. Below, the first 2 records are being reported from the imported history. The current value of SAL is 1000 for EMPNO=1.

SQL> SELECT  versions_endscn, versions_operation,  emp.*

FROM FDB_USER.EMP versions between scn minvalue and maxvalue

where empno =1 order by versions_endscn  nulls last

  2    3    4  /

VERSIONS_ENDSCN V      EMPNO        SAL

--------------- - ---------- ----------

         560873 U          1        800

         562013 U          1        900

                           1       1000