Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Archiving Oracle table audit data with partitioned tablespaces

Oracle Database Tips by Donald Burleson

Today we are challenged to find a way to store a complete historical record of row changes.  We can buy pre-written software (the Lumigent product), or write your own with standard DBA tricks.

Assume that we adopt a "current table ==>  history table" strategy where we enhance the SQL update command with a pre-process.  Before we change a row, we use a "before update and before delete" trigger to archive all "old" row values (similar to the BEFORE (BFOR) row image).  We write the old row values to a corresponding history table, where the columns are identical, and the only difference is the suffix on the table_name of "_history". 

We record the date-time of the row change in the history table by using rowscn for tracking timestamp for changes to the row.  This is done with the "row dependencies" in the Oracle 10g rowscn psuedocolumn.

  • Wasted space - We archive the whole rows, and not just the single column that changed.  It's simple (because table columns are identical and the before update/delete trigger is simple), but we are trading-off code complexity for disk storage.

  • Storage Management - Of course, whole-row audits audit tables may grow VERY large, and we need to look at a partitioning scheme whereby we can use the tablespace transport (Oracle transportable tablespaces) utility to move the  "the oldest generation"  to cheaper read-only media like optimal disk.  This is like a Generation Data Group (GDG) in MVS, except that we are tracking tablespace partitions (and their datafiles). Using this archiving method, all historical tables are still available to Oracle for reading (no writes in a read-only tablespace), they are only moved to less expensive "warehouse type" storage.

Providing a simple yet complete audit of Oracle table row changes can consume huge amounts of disk, and this may not be appropriate for high update/delete environments (high DML).  There are many third-party products, tools and techniques (including archived redo logs) to provide DML auditing for tables.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.