I recently started work on a small APEX application. The owner of the application requested that all data changes be tracked, which was not an unreasonable request given the nature of the application. In days of yore, i.e. pre-Oracle 12c, this would have involved something like custom PL/SQL code within an data access API or a (*shudder*) trigger-based strategy. However, recalling back to Bjoern Rost’s presentation at the 2014 AusOUG conference I decided to look into using Flashback Data Archive (FDA) instead… and I’m so glad I did!
Flashback Data Archive essentially involves defining a retention policy, e.g. something like “1 year”, and associating tables with that policy. Oracle then handles the rest. Let’s take a look at how easy it is to set this up. We will:
- define a new tablespace
- create a FDA policy (specifying the new tablespace as the storage)
- create an FDA application (which is nothing more than a container for tables to allow easy FDA management; 12c only)
- associate the application with the FDA policy
- enable the FDA
An example is in order and we’ll use Oracle standard DEPT and EMP tables for it (using a database of version 22.214.171.124). Assuming you’ve already got the table set up in the SCOTT account we’ll create an FDA to track 2 years of data changes:
-- Create the FDA tablespace CREATE TABLESPACE scott_fda_data DATAFILE '<<path>>/scott_fda_data.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 200M / -- Grant SCOTT access to the new tablespace ALTER USER scott QUOTA UNLIMITED ON scott_fda_data / -- Create the flashback data archive policy CREATE FLASHBACK ARCHIVE scott_fda TABLESPACE scott_fda_data QUOTA 200M RETENTION 2 YEAR / -- Set up an Application so we can control FDA for all tables (Oracle 12c only) EXEC dbms_flashback_archive.register_application('SCOTT','SCOTT_FDA') EXEC dbms_flashback_archive.add_table_to_application ('SCOTT','DEPT','SCOTT') EXEC dbms_flashback_archive.add_table_to_application ('SCOTT','EMP','SCOTT') EXEC dbms_flashback_archive.enable_application ('SCOTT')
Now that we’ve got our FDA in place, let’s make some data changes. The script below makes use of DBMS_LOCK.SLEEP to insert delays of 1 minute. This is done so that later on we can query the tables as they looked back in time using semi-realistic examples.
UPDATE emp SET sal = sal + 10; COMMIT; EXEC dbms_lock.sleep (60) INSERT INTO EMP VALUES (8000,'MARK','MANAGER',7839,to_date('01-01-2015','dd-mm-yyyy'),2000,NULL,40); COMMIT; EXEC dbms_lock.sleep (60) INSERT INTO EMP VALUES (8010,'JAMES','DEVELOPER',8000,to_date('15-01-2015','dd-mm-yyyy'),2500,NULL,40); COMMIT; EXEC dbms_lock.sleep (60) INSERT INTO EMP VALUES (8010,'HEATHER','DEVELOPER',8000,to_date('20-01-2015','dd-mm-yyyy'),2200,NULL,40); COMMIT; EXEC dbms_lock.sleep (60) UPDATE emp SET sal = sal + 500 WHERE empno = 8000; COMMIT;
Of course, there’s little point tracking data changes if you can’t easily access them. Simple flashback query clauses to SELECT allows us to query the source tables either as it looked at a specific point in time or show all the changes that have occurred across time.
For example, to view the data in a table at a specific point in time we use:
SELECT * FROM <table_name> AS OF TIMESTAMP <timestamp_expr>
So, for our example table we could run something like the following to find out what that the EMP data was like 5 minutes ago, just before we did the operations shown above:
SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) ORDER BY empno;
If you are wanting to get a full version history of the changes made then you can use the VERSIONS BETWEEN clause:
SELECT e.* , versions_starttime , versions_endtime FROM emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE e WHERE empno = 8000 ORDER BY versions_starttime;
The above query includes the pseudo-columns versions_starttime andversions_endtime, which provide the date range that a specific row version was effective for.
Now for some FDA details not mentioned above:
- Flashback Data Archive is available in all versions of Oracle from 126.96.36.199 without additional licensing. In earlier versions of 11g it required Advanced Compression license to utilise but that restriction has been removed now the archive tables are, by default, not compressed.
- Only Oracle 12c supports the ability to create flashback applications. In 11g you associate the table directly with the FDA one by one using ALTER TABLE <<table>> FLASHBACK ARCHIVE <<fda>>. This means the FDA tracking won’t be started/stopped at precisely the same moment in time for all tables.
- You’ll need to disable FDA whilst making DDL changes to the tracked tables; dbms_flashback_archive.disable_application
- In order to query the tables as at an earlier time period you require FLASHBACK privilege on the table, or the FLASHBACK ANY TABLE privilege.
In summary, Flashback Data Archive provides an effective means of tracking data changes on a table. The implementation is not only simple and the interface for accessing the historical data, via extensions to the SELECT statement, is easy to harness and intuitive.