Home / server / Questions / 12948
In Process
Rob H
Asked: 2009-05-28 06:24:00 +0800 CST

Linux database diff tool

  • 772

I'm looking for a database schema comparison tool that runs on Linux, handles Oracle databases, and can generate a synchronization script to migrate one schema to another. TOAD does this very well on Windows, but doesn't run on Linux. Free and open source is preferred, but not required. It would also be nice if it is scriptable, enabling it to be run as a cron job, for example. Anything like that out there?

BTW- I have looked at Oracle's own SQL Developer tool, and although it supports comparisons, there were a lot of false positives in my tests. For example, it flagged foreign keys that were identical as being different.

linux database oracle

6 Answers

  1. I have spent a lot of time looking for something similar and never found a completely satisfactory solution. That being said, you might find Liquibase (www.liquibase.org) to be useful.

    • 2

  2. Try SchemaCrawler, my free, commandline, open-source tool, written in Java. It is OS and database independent. It is designed to find differences between schemas (and even data). There are a JavaScript and templating language interfaces that you can use to generate your own scripts.

    • 2

  3. Googling for "schema comparison tool" produced this tool available on the dbsolo.com website. The url to the site is www.dbsolo.com/schema_comparison.html. I don't have enough permissions to post an actual link yet, sorry.

    It's a commercial tool but it claims to be able to compare schemas for these databases: Oracle, SQL Server, DB2, PostgreSQL, MySQL and Sybase ASE/ASA.

    Additionally it runs on these platforms: Windows, Linux, MacOS and Solaris.

    I can't endorse it since I've never tried it but it does include a 30 day free demo so you could try it out to see if it suits your needs.

    • 1

  4. There are a lot of tools, satisfying to different degrees, none perfect :(

    I'm using Aqua Data Studio as it's in pure java and can connect to most of databases.

    • 1

  5. DBSA (DataBase Structure Analysis) is a tool for comparing schema snapshots. Differences are reported and an SQL patch can be generated. It includes a basic repository facility for schema history tracking.

    • 1

  6. I think the tool you're after is Oracle Data Pump. I blab on about it because Oracle provides a good tutorial for it here. The tools specifically are...

    • expdp for exporting the data
    • impdp for importing.

    To do a "diff" between 2 tables (with identical columns), betweeb 2 separate databases...

    Select table_name from dba_tables \
  where owner='OWNER_NAME' \
    minus \
      select table_mame from dba_tables@remote_database \
        where owner='OWNER_NAME'

    You need to create the database link in the first database obviously. This is probably only a partial solution to what you're after though, but I can't come up with anything better. :)

    • 0