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 









Creating an Oracle Data Cleansing Architecture

Oracle Database Tips by Donald Burleson

As we noted, replicating the decision processes of a knowledgeable professional can appear to be a daunting task, but daily successive refinement of the data scrubbing program can yield remarkable results. 

There are several cardinal rules for data cleansing:

1 - Keep a complete audit table - For every row that was unified or scrubbed, record the record ID (Using the new Oracle10g row timestamp feature), and record the old value, the new value, the date of the change and the person approving the change.

2 - Facilitate the Data Quality Officer (DQO) - Successful data scrubbing system create a simple online interface (using tools such as HTML-DB), and have the interface allow then to implement or reject the proposed changes.

There are two "attack" approaches to data cleansing, each one appropriate for the type of database:

  • Brute force bots - These are background processes that run during low-usage period that bubble through the data looking for "questionable" data.  The rules are programmed, and this is a rudimentary form of data mining.  These low-impact bots run quietly in the back ground, constantly scouring your database, looking for "fishy" data.  The brute-force approach is only appropriate for Oracle environments where excess RAM and CPU are available, (to accommodate the PGA memory region and overhead of the compiled PL/SQL logic).
  • Targeted jobs - The targeted jobs examine the data manually and start by recognizing the characteristics of data anomalies. 

We also have to decide "when" to scrub the data, a row-at-a-time when it enters the database (via ETL), of in a batch, say, once per day:

  • Row-at-a-time mode - In this approach we unify and Scrub the data during ETL, one record at-a-time, not allowing any questionable data into the database.  The downside to this approach is the massive amount of work and the possibility 6that a later records in the ETL feed might resolve the anomaly.
  • Batch mode - This approach runs a periodic batch job to unify and cleanse the day's new information.  This is the preferred approach for many Oracle database because it can be performed during idle midnight hours and the nature of data scrubbing is well-suited to a batch approach.



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.