Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
By content I mean data not schema. Sorry I wasn't clear. – albert green Sep 20 '11 at 19:04
are these databases supposed to be the same? Is this some variety of asynchronous replication? Or is only some of the data supposed to be the same in each database? And is the data numbers, CLOBS, XML? – kevinsky Sep 20 '11 at 20:36
we have 1 database per environment. we have many envs. each database has 30 tables. the databases are exactly the same in terms of structure. the data is different per database. each table has 10 columns max. no CLOBS, no XML, just simple data types. we need to find the delta in the data between a source and target database. – albert green Sep 20 '11 at 22:28

Here is a brute force example that doesn't require modifications to the tables. It merely gives you information about what is different, it does not do anything to synchronize it.

Sample data:

--DROP TABLE tableA;
--DROP TABLE tableB;

   SELECT level-1 ID, chr(level-1+70) bb, chr(level-1+100) cc FROM dual CONNECT BY level<=4

   SELECT level ID, chr(level+70) data1, chr(level+100) cc FROM dual
   SELECT level+2 ID, chr(level+70) data1, chr(level+100) cc FROM dual CONNECT BY level<=3



WITH UnionedRows AS
   SELECT * FROM tableA
   SELECT * FROM tableB
SELECT ID, sum(MyCount),
      when sum(MyCount) = 111 then 'In Table A and Table B - Identical.'
      when sum(MyCount) =  11 then 'In Table A.'
      when sum(MyCount) = 112 then 'In Table A and Table B - Different.'
      when sum(MyCount) = 101 then 'In Table B.'
      else 'Error.'
   end Status
   SELECT ID, count(*) MyCount FROM UnionedRows GROUP BY ID
   SELECT ID, 10 FROM tableA
   SELECT ID, 100 FROM tableB

You will need to modify this code to use your primary keys, table names and database links, but the nice thing is that you don't need to list all the column names for every table.

share|improve this answer

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),
  • 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);
    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 > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
    EXECUTE IMMEDIATE v_execute;
    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 > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
    END IF;
    --add exception handling here-log all errors to another table and RAISE error again

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
share|improve this answer

Compare schema (link1, link2) or compare data?

share|improve this answer
How ... how does that help? Can you give better answers in case those forums disappear one day? – jcolebrand Sep 20 '11 at 20:19
@jcolebrand: How does comparing schema help? It doesn't, but the answer was posted before the OP specified what he wanted compared (hence my request for clarification). I do not think that the oracle forums are going to disappear anytime soon. – Noam Kremen Sep 21 '11 at 7:33
We just like to have a policy on the stack exchange network of sites that answers that only link to other sites are considered of poor quality, and should be either enhanced or deleted. I see that you're fairly new on the network, so thought I would start with asking how this helped. However, link-only answers are frowned upon (and this seems to be a link only answer) – jcolebrand Sep 21 '11 at 14:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.