Wanna keep track of changes to a table, and be able to query different versions of your data? Then check out Oracle’s ‘new’ feature – FLASHBACK ARCHIVE. From Oracle (docs)
“…which provides the ability to automatically track and archive transactional data changes to specified database objects.”
(BTW Whoopie has yet to portray a DBA, but we can keep our fingers crossed.)
Automatically tracking and archiving data changes must have some sort of catch, right? What if someone drops, renames, or truncates my table!?!
Oracle will step in and say –
‘ORA-55610 – Sorry Charlie, you can’t do that to this table because the data is really, really, important.’
Ok, the real message is a bit more cryptic, but you get the idea. Or maybe you don’t, here is the documentation for what you CANNOT do to a table once it’s been tagged with the FLASHBACK ARCHIVE clause.
Let’s step through a quick example using my favorite table (and drink!):
1. Create your Flashback Archive (several requirements, read those here)
[sql]
CREATE FLASHBACK ARCHIVE TEST
TABLESPACE "USERS"
QUOTA 50 M
RETENTION 3 DAY;
[/sql]
Quota and retention do just what they sound like. Limit the amount of space and time reserved for tracking changes.
2. Create or Alter your table with the ‘FLASHBACK ARCHIVE TEST’ clause.
3. Change some of your data.
[sql]
UPDATE BEER X
SET X.City =
SUBSTR
(
City,
2
);
[/sql]
4. Start playing!
How much space is being consumed by the data changes tracked?
[sql]
SELECT T.Owner_name, T.Table_name, S.Tablespace_name
, SUM (S.Bytes) Total_space
FROM Sys.Dba_flashback_archive_tables T
, Sys.Dba_segments S
WHERE T.Owner_name = S.Owner
AND T.Flashback_archive_name = ‘TEST’
AND S.Segment_name LIKE
‘SYS_FBA%’
|| SUBSTR
(
Archive_table_name
, INSTR
(
Archive_table_name,
‘_’
, -1
, 1
)
+ 1
, LENGTH (Archive_table_name)
)
GROUP BY T.Owner_name
, T.Table_name
, S.Tablespace_name
ORDER BY 1
, 2;
[/sql]
Before you object that my code is buggy with the commas being all wrong, please read this #shameless_plug.
Looking at the old version of the data, prior to the update.
Oracle gives us the ‘AS OF’ clause, which allows you to query the data at a particular point in time (docs and examples)
[sql]
SELECT City, LENGTH(City) FROM BEER
AS OF TIMESTAMP
TO_TIMESTAMP(‘2011-06-22 12:00:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE City LIKE ‘%Raleigh%’;
[/sql]
The same rows, but today’s data
Same query, but without the ‘AS OF TIMESTAMP’ clause
Performance Concerns
Immediately after altering my BEER table to being tracked with flashback, I noticed a performance degradation. Oracle plainly lists a series of best practices and recommendations to ensure maximum performance. I strongly advise you read and implement (test!) them before using this feature in a production database.
To get an idea of how this might affect the plan, let’s look at the plan of each of my queries from above.
Can you guess which plan belongs to which query?
