Posted by André Araujo on Oct 9, 2009
This post originated from a quick discussion we had internally on how to quickly and easily compare schemas between two Oracle databases. I learned about Sheeri Cabral’s post with a quick comparison solution for MySQL databases and I though of using a similar approach for Oracle. I did some testing and it worked quite well.
There certainly are tools in the market, free or not, that do this for us, and even generate scripts to correct differences. The steps below only go as far as to tell you what the differences are. However, they don’t require any additional tool and can be easily executed in any *nix or Windows environment.
The procedure uses the Oracle Data Pump utilities to extract one schema’s metadata and the Unix diff command to compare them. The metadata files generated by Data Pump, though, contain a lot of information about tables and indexes statistics that add too much noise to the diff output, since they usually differ a lot between databases. To eliminate that noise, I used the following small Perl script to pre-process the metadata files: Read the rest of this entry . . .
Posted by Sheeri Cabral on Sep 30, 2009
The easiest way to see the differences between two schemas on a non-Windows machine is to run:
mysqldump -h server1 --no-data --all-databases > file1.sql
mysqldump -h server2 --no-data --all-databases > file2.sql
diff file1.sql file2.sql
However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.
We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.
We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and patch. MySQL workbench does both reverse engineering and “forward engineer” alter script for free in the OSS/Community version.
Step by step, here’s how to do it:
- First, create a script with the database schema that you want to be the final result. For example, if you want to take an existing production schema and change it to be like the development schema, then your “final result” is the development schema:
Read the rest of this entry . . .
