Feb 14, 2017 12:38:00 PM by John Dorlon
Available starting in Toad 22.214.171.124. Toad Expert Edition or DB Admin Module is required
For a long time, Toad has had a window that lets you compare table/view/mview data, and Sync it if you have the Toad Professional Edition or DB Admin Module. That's fine and good, but if you have lots of tables to compare, you have to set it up for each table, and run each table individually. Wouldn't it be nice if you could just point Toad at a couple of schemas and have in sync all (or some) of the data in the tables of those two schemas? Well, now you can!
To get started, go here...
..and you'll see this
I've made this window as simple as I can.
The "Syncronization" group box is here on the first tab, next to the schema selection, so you won't have to go to any of the other tabs to see what will happen when you click the green triangle to run it. We're going to synchronize the data in your tables (or at least create a script to do that). There is not an option to review the differences first. If you want that, then go Database -> Compare -> Data.
To sync data in all (compatible) tables in a pair of schemas, all you really need to do is pick the schemas and click the green triangle at the top. There are some options on the other tabs, but they default to what I think are the most useful settings.
Before we just blaze on though, here are some things that you should know. There are two different techniques of comparison that this window can perform. I've optimized both of them as much as I can, but one is much faster than the other. And which technique Toad will use all hinges on what you do on this first tab. So here's what you want to do:
I've named the Same-Connection-or-through-DB-Link technique "Comparison by SQL", and the Fetch-all-data-to-your-PC technique "Row by Row Comparison". Remember that, because I'll refer back to them by these names later, and you'll see it in the GUI too.
As a point of reference, in my tests, comparing and syncing a 1 million row copy of DBA_OBJECTS -
In the same connection, it takes only a few seconds.
In different connections and across a DB Link, it takes about 10-15 seconds.
In different connections without a DB Link, using Array DML, it takes about 8 minutes and 550MB RAM.
In different connections without a DB Link, and NOT using Array DML - it really depends on how many rows need to be synchronized. If everything matches, it'll goes as fast as Array DML. If there percentage of rows that need to be synchronized is high, then it might take up to 30 minutes for a table this size.
Every environment is different, so your compare and sync times will not be the same as mine.
Next tab. Probably Optional.
To compare all tables with matching names in your schema pair, do nothing on this tab. "Match tables automatically" and "Table names match exactly" are checked by default, which is what you want.
You only need to do something here if your table names don't match exactly, or if you only want to compare some of the tables. Hopefully this looks familiar - I copied it from Toad's "Compare Multiple Schemas" window.
So here's what the manual table matching looks like:
To see a list of tables to be compared (even if it's set to automatic), click "Load". If you want to match them up manually (maybe their names don't match), you can use the dropdown in the "Target" column as shown above. To check their compatibility for compare and sync, click "Validate". There's a list at the end of this blog detailing what might disqualify a table for compare and sync.
Options tab. Optional. (hence the name)
I think these are all pretty self explanatory so I'm not going to comment on every one of them, but I do have a few comments.
When you click the green triangle to run the compare and sync, you'll automatically be taken here. There's a row for each table to be compared, and Toad will make two passes through the list. The first to make sure tables are compatible and disable foreign keys, and the second pass to actually do the compare and sync. The screen shot above shows a "Comparison by SQL" run. The compare and sync is done in background queries, and we display how many rows are deleted and merged (merge includes both insert and update, and we don't have details about how many rows of each).
"Row By Row" comparison
Row By Row comparisons have details about rows inserted, deleted, updated, and number of matching rows, which are updated once per second as the compare/sync progresses. . There is also an "estimated time to completion" countdown on each step (one step being deletes, another being insert and update).
Finally, here's a chart showing various conditions that may cause a table to be incompatible for comparison:
|(Potential) Problem||Supported in Row By Row Comparisons||Supported in Comparison by SQL|
|LONG, LONG Raw datatype||Yes||No|
|Nested tables and arrays||No||No|
|CLOB, NCLOB, BLOB, XMLTYPE||No||Yes, if not using DB Link|
|All other datatypes||Yes||Yes|
|Columns in diferent order||Yes||Yes|
|Missing Primary Key||No||Yes|
|Different Primary Keys||No||No|
|Extra columns in source||Optionally, Yes||Optionally, Yes|
|Extra columns in target||Optionally, Yes||Optionally, Yes|
Edit: In Toad 12.12, the look and feel of this window is improved, and Identity columns are no longer a problem for the compare/sync. These columns will just be ignored since they can't be updated.
Tags: Toad for Oracle
Written by John Dorlon