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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational