Compare content of databases in Oracle We have a request to build an ASP.Net application that will be used as an interface to launch commands on Oracle databases.

We need to find a way to compare the content of 2 databases. We prefer not to code this process. Instead we would like to rely on Oracle to do it (we would like to call Oracle commands).

What is the best approach for this?

We are using Oracle 10.x

Any help will be appreciated
This is what you could do


for each table in each database in each environment add a column called LAST_MODIFIED_DATE TIMESTAMP(6)
add a before insert or update trigger to set LAST_MODIFIED_DATE=CURRENT_TIMESTAMP for each table
be sure the timezone of each database is correctly set in the NLS Parameters or you will be getting odd answers.  I assume each database is in the same timezone
create a table in a logging schema to record the list of changed values, something along the lines of 

CREATE TABLE delta_changes
( P_KEY NUMBER(20) 
table_name VARCHAR2 (250),
id NUMBER(10),
change_date TIMESTAMP(6),
logging_date TIMESTAMP(6) DEFAULT CURRENT TIMESTAMP(6));

and create a sequence for the primary key and a pre insert trigger on delta_changes
create a database link between the two databases
create a packaged procedure along these lines
create a job to run the packaged procedure on a schedule of your choice

PROCEDURE  get_changes(table_name_in := NULL)    is

CURSOR all_changes is
SELECT table_name
FROM dba_tables
WHERE owner =  your_schema;
v_execute  VARCHAR2(1000);
BEGIN

IF table_name_in IS NULL THEN

FOR EACH t_name in all_changes LOOP  
v_execute := 'INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||t_name.table_name||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||t_name.table_name||', '||t_name.table_name||'@TheOtherDatabase WHERE '||t_name.table_name||'.primary_key = '||t_name.table_name||'.primary_key||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE &gt; t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
EXECUTE IMMEDIATE v_execute;
END LOOP;
ELSE
v_execute :='INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||table_name_in||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||table_name_in||', '||table_name_in||'@TheOtherDatabase WHERE '||table_name_in||'.primary_key = '||t_name.table_name||'.primary_key'||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE &gt; t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
END IF;
--add exception handling here-log all errors to another table and RAISE error again
END GET_CHANGES;



This solution has a number of ways it could be improved such as:


use bind variables
which users should have execute on this packaged procedure?
or use Oracle advanced queues to log the changes on one database and then extract them to compare on the other
please excuse my syntax as I'm doing this by memory