Take the 2-minute tour ×
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. It's 100% free, no registration required.

we have a request to build an ASP.Net application that will be used as 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
1  
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
add comment

3 Answers 3

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

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;

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

CREATE TABLE tableB AS (
   SELECT level ID, chr(level+70) data1, chr(level+100) cc FROM dual
   UNION ALL
   SELECT level+2 ID, chr(level+70) data1, chr(level+100) cc FROM dual CONNECT BY level<=3
);

SELECT * FROM tableA;
SELECT * FROM tableB;

Compare:

WITH UnionedRows AS
(
   SELECT * FROM tableA
   UNION
   SELECT * FROM tableB
)
SELECT ID, sum(MyCount),
   case
      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
FROM
(
   SELECT ID, count(*) MyCount FROM UnionedRows GROUP BY ID
   UNION ALL
   SELECT ID, 10 FROM tableA
   UNION ALL
   SELECT ID, 100 FROM tableB
) GROUP BY ID ORDER BY ID;

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
add comment

Compare schema (link1, link2) or compare data?

share|improve this answer
1  
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 N. 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
add comment

Your Answer

 
discard

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.