We have an ORACLE 10g database with 100+ tables. The software used against this database is an order entry system.
We are looking for some software (preferably free) or script which when ran will tell us which tables had a new row INSERTED or any data UPDATED....after we have generated a new 'order' from within ... Also which sequences were used/increased.
Basically a kind of DB DIFF tool but for data instead of usual DIFF on structure/scehma.
thanks
What are you comparing against? Do you do an export of the db (otherwise how do you know what point in time to compare against)?
I don't know how useful this is, but according to this page Toad for Data Analysts can compare table data.
I think a better approach might be to use triggers to write to a log table:
Then you will have a history of actions that you can query
Try the following;
http://www.toadsoft.com/ -- Shareware
http://www.dkgas.com/oradbdiff.htm
http://www.softtreetech.com/dbtools/
http://www.impacttoys.com/supported_ora_features.htm -- Free
If you need only need this functionality on a few tables and you'll be processing the change information in a batch/reporting mode, you might want to consider Oracle Workspace Management, which is built into the database. When you enable workspace management on a table Oracle replaces the table with a set of views and triggers built around a converted version of the table with extended attributes. You'll be able to see the history of every DML operation by querying the converted table and the appropriate attributes.
A somewhat lighter-weight solution would be to enable fine-grained auditing on the tables of interest - this will capture the SQL involved, which you could parse for INSERTS, UPDATES, AND DELETES.
Both of these are aimed at capturing changes without creating your own audit structures and coding triggers for every table.
DB Diff for Oracle is old and a bit clunky, but it works well enough.
If this is a one-off to try and work out what 'order generation' is doing under the tables, look at tracing the session.
http://www.juliandyke.com/Diagnostics/Trace/EnablingTrace.html
This generates a file on the server, normally in a udump directory (potentially in bdump, but unlikely). You can run a oracle tool tkprof to format it into an easier to read output file. That will contain all the SQLs issued by the session.
This is a better approach to understanding the application because you get to see the actual SQL that executed (even if it didn't do anything).
An Oracle feature which may help, Flashback Query. Have a look in the Oracle Documentation for how to enable it, and use it. Here is a link to a quick example Flashback Query.
An excellent new DB DIFF tool is PowerDIFF for Oracle. Works for STRUCTURE and DATA. See http://www.orbit-db.com.
CompareData is free for comparing table/view data. You can even schedule the data comparison (free). But comparing Oracle sequences would require registration after 30-day evaluation period expires.
Take a look at Devart's dbForge Data Compare for Oracle, a free GUI tool for data comparison and synchronization, that can do these actions over all database or partially.
By the way, today we have released a new major version of our dbForge Data Compare Tool for Oracle - 2.00.
New version includes a big list of new features, and now is available in two editions - Express (free) and Standard (30-day trial, $149.95).
Visit product page and list of new features on our website to get more information.
Red Gate Software now has tools for compaing both Oracle schema and data. Schema Compare for Oracle allows you to compare and synchronize the schemas of Oracle databases. It currently supports 9i, 10g and 11g.
You can download and try out for free the EAP of Data Compare for Oracle for comparing table data.
Tom Harris, Red Gate Software