Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
Have you tried running TOAD on Wine in Linux? –  Brettski May 27 '09 at 16:14
    
No. Virtualizing Windows is a backup plan, but I'd much prefer a native tool. –  Rob H May 28 '09 at 17:48
    
Wine is not virtualizing windows. It's running natively in a simulated Windows environment. Wine makes your program think it's running in Windows by providing all of the normal Windows-y things, even though it's actually Linux. I've found that running programs in Wine is generally very painless and a great alternative to running a Virtual Machine. –  Justin Bennett Jun 5 '09 at 12:09
add comment

6 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Hmm... I hadn't seen this one. Will take a look! –  Rob H May 29 '09 at 23:56
    
Unfortunately, the trial version crashes consistently on my Windows box. No go. –  Rob H Jun 4 '09 at 18:49
    
Why are you trying it on your Windows box? You've clearly stated you want a Linux tool. –  hark Jun 10 '09 at 15:13
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
This project is listed as "pre-alpha". That's not suitable for a production environment. –  Rob H Jun 1 '09 at 20:27
add comment

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. :)

share|improve this answer
    
This tool is for importing and exporting data. That's not what I'm trying to do. I'm trying to detect changes between two schemas and generate a synchronization script to migrate one to the other. –  Rob H May 28 '09 at 17:45
    
I'll ask the DBAs at work then - that's about the end of my DBA knowledge :) –  Xerxes May 28 '09 at 23:30
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.