Re: Comparing 2 schemas in Oracle 8.1.7 for Windows NT/2000

We just evaluated Oracle Change Mangler (which I'll affectionately refer to as OCM), Quest Schema Mangler (QSM), and Embarcadero DBArtisan Change Mangler (DCM). All of these, in theory, generate DDL to synch and DML to preserve data. In practice, none of them do it with 100% accuracy. We ended up buying DCM. It supported the most Oracle features, and did the best job (although it did require multiple passes to get it right). In a nutshell, all these tools suck: DCM just happened to suck the least.

Surprisingly, OCM supported fewer Oracle features than the third party tools. OCM has an advantage in that it is written in Java, so it'll actually run on any platform. One drawback was that you have to have the whole Oracle Enterprise Mangler infrastructure set up and running. Another drawback was that it produces a script that is ORATCL, which (according to Oracle) can't be run outside of OCM. Actually, it can be run from ORATCLSH.exe (a console-mode Oracle TCL shell), but we just couldn't figure out how to do it, since Oracle doesn't support it nor provide any documentation. OCM doesn't support FLOAT datatype, LOB's, partitioned tables or indexes, global temporary tables, index-organized tables, etc.

QSM was by far the slowest, but the second most accurate. QSM puts everything into the repository before it does a compare, and this can take quite a while. It doesn't support global temporary tables, but it does generate PL/SQL to re-insert data, which is a plus (does commits every 100 rows). I'm not sure about the other 8.1 specific objects. The synch script is DDL and PL/SQL, which means you can save it and run it from SQL*Plus (ie. it's repeatable).

DCM supports global temporary tables, supposedly supports IOT's, partitioned tables and indexes, etc. (although we don't have any of those object types at the moment to test it on). It's also the fastest in doing a compare, and it doesn't require a repository. It actually takes advantage of some newer features (such as ALTER TABLE...DROP column) when possible. DCM is (by far) the least expensive of the three tools. The only really irritating issue is that it takes multiple passes (ie. compare/synch/compare/synch...) to get everything synch'ed. Since the compare and synch is very fast, this is an issue that we can live with. The synch script is DDL and DML, so it's saveable and repeatable. DCM gave us the best results of the three.

With any of these tools, they will still occasionally miss something, and you just have to create a script by hand to fix it (and submit an enhancement request to the vendor!)

Here is what I'd suggest: Get copies of all three of them, and run test cases. Use one tool to compare/synch, then re-run the compare with all three tools. Start with the smallest schema with the most variety you can. As you find things that one tool doesn't do correctly, create a really simple test case, and use that to compare all three, and engage the vendor for help. Give the vendors only the small concise test cases, 'cause tech support folks get confused with this easily (it's actually a pretty darned complicated thing to do in practice.) Document everything that you do, because your results will probably be different that the results I got (depending on what types of schema objects you're using).

One more piece of advice: compare only physical segments if you possibly can (ie. tables, columns, constraints, indexes, etc.). Everything else (views, procs, functions, paks, etc.) can be CREATE OR REPLACEd by the original DDL scripts. Tables are the only object where you really have to preserve the data (even the constraints and indexes can be dropped and rebuilt by DDL scripts if necessary). If you don't compare the REPLACEable objects, the compares will take a LOT less time.

Hope this helps (I know your pain!).

Best regards,
Dave

John Smith <jsmith_at_hotmail.com> wrote in message news:iyPV6.248873$Z2.2878377_at_nnrp1.uunet.ca...
> Hi,
>
> I need a tool to identify differences between (let's say) a DEV schema and
 a
> PROD schema, and generate the DLL and DML to apply those changes on PROD
> while preserving the data. I would like to use some of the tools available
> on the Market but not sure witch one is the best: Oracle Change Manager
> V2.2, Quest Schema Manager V3.2c1 or DB Artisan Change Manager V1.3. I'll
> appreciate any help
> John
>
>
>
>
Received on Wed Jun 13 2001 - 21:21:54 CDT