13.2.0.149 Schema Compare: Sync columns with DBMS_REDEFINITION

Toad for Oracle - Beta
1

When doing a schema compare, it's identified a table that has an extra column in the source schema. When I run it with the "Add/Reorder columns with DBMS_REDEFINITION" option unchecked, the sync script correctly comes up with something along the lines of:

Set define off;
ALTER TABLE [schema].[table] ADD ([column] [type]);

However, if I do it with "Add/Reorder columns with DBMS_REDEFINITION" checked, the sync script is basically empty, just containing:

Set define off;
2

I just tested this and for me it's working correctly.

My source schema table looks like:

CREATE TABLE TBL1
(
A NUMBER,
B NUMBER,
C NUMBER
);

and my target table is

CREATE TABLE TBL1
(
A NUMBER,
B NUMBER
);

Then when I run schema compare, I get this for the sync script, regardless of the add/reorder option.

ALTER TABLE COMP2.TBL1
ADD (C NUMBER);

Does it work correctly for you with simple tables like this? Maybe there is some other factor involved.

What Oracle server version are you using?
Do you have privileges on DBMS_REDEFINITION?

if it works for you with this simple case, can you send me details to reproduce your more complicated case that does not work?

Thanks

3

I've done some more testing and I think it's maybe more of an issue with the way I interpreted the UI on the Difference Details screen :slight_smile:

Using your example, the Difference Details shows:

  • Middle section: 1 object only in [source]: Column C
  • Bottom section: 1 objects which differ: TBL1
    Both of these are checked by default and the sync script is as expected.

In my real-world example, I had a lot of differences (:cry:), so wanted to do it in several stages and only sync up a few differences at a time, so I unchecked the top and middle sections, just wanting to start with a few objects from the bottom section

Going back to your example:

  • If it's checked in both the middle section ("1 objects only in [source]") and the bottom section ("1 objects which differ"), the sync script is OK
  • Starting with both checked, if you uncheck it from the bottom section, it automatically unchecks it from the middle section and the sync script is empty (makes sense - this'd sync up any table changes but not add the missing columns)
  • Starting with both checked, if you uncheck it from the middle section, it will leave it checked in the bottom section and the sync script is empty. This wasn't what I expected, but now I can see why - I'm asking it to sync the table but not add the missing column
  • Starting with both unchecked, if you check it in the bottom section, it automatically checks it in the middle section (not sure if I expect this or not)
  • Starting with both unchecked, if you check it in the middle section, it doesn't automatically check it in bottom section, but it does add it to the sync script (expected)

Some of the combinations of automatically (or not) checking/unchecking kinda feel a bit inconsistent, but I think it's probably OK now I understand it better

Thanks,

4

ah, ok.

Yeah, having an column in one table but not another - there's kinda two ways to think about it (tabl has differences or column is "in one schema but not the other"). I think in the early days of schema compare, we put it in both areas just so the difference could be found no matter which section you look in, but it got a little weird once we added the ability to check/uncheck those to tell Toad what's in the sync script.

I'm glad it makes sense now.