Compare Schemas: Difference Details Tab - Detail View

Follow / 13 Jul 2018 at 2:44pm

One of the features of the older TOAD's comparison tool was that it allowed for the details of the comparison to be seen.  This made it easier to see exactly differences were being addressed by each of the items.  In the current Compare Schema, the functionality to see the details is there, but it requires a mouse click to pop-up the screen, and another to remove the screen.  When each difference is being reviewed and there are hundreds of differences, this makes the review process take much longer than required.

What I would like to see changed is on the "Difference Details" tab, to make the it into a split screen, such that the existing Tree View would be displayed on the left hand side, and on the right hand side that two more panes be displayed with right top pane containing the detail view and the bottom pane showing the DDL Script.  This would eliminate streamline the review process and drastically reduce the amount of effort required for large reviews.

On a separate topic, TOAD is currently not supporting an automated approach to re-order columns of a table if DBMS_REDIFINITION is not available.  (our DBAs will not allow us to use the DBMS_REDIFINITION package because of security concerns).  What I would like to see is an option that would create a script like this:  Rename Table to some temp name, drop all triggers, FK, indexes etc from the table, create table using correct ordering of columns, add indexes, triggers, FK, etc.  These are the steps that we are doing manually.  Obviously, the DBMS_REDIFINITION package would be the preferred approach is it doesn't require downtime, but at least if this were a choice, then the manual work to recreate the table could be eliminated.

Thanks for listening and love the tool!

David

Replies

  • Follow / 16 Jul 2018 at 9:09am

    Hi David,

    I'm wondering about your need to run DBMS_REDEFINITION. Why would you need to reorder the columns of a table? I can understand that you might have problems getting the DBA to approve its use, but unless you have some varchar2 data types that star small and end up being updated to large, that need moving to the "end" of the row, I'm puzzled as to why you need to reorder.

    Just wondering.

    Cheers,

    Norm [TeamT]

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 16 Jul 2018 at 2:08pm
    The simplistic answer is that it is our policy to keep all of our 10 environments (DEV(s)/TEST(s)/PROD) synchronized (including column order).  This is normally accomplished through the promotion/migration process, but at times one change may have a different timeline than another resulting in a column coming before another.
     
    Now why that is our policy is that some (very small number) of the code/scripts uses star(*) for the columns when copying data between two tables.  For example, INSERT INTO TEST1 SELECT * FROM TEST2.  There are other cases as well where the order of the columns matters… 
     
    Having code like this is both a good and a bad thing.  It is good in that when we add columns to the tables that we don’t have to modify the code, rather we just have to ensure that both TEST1 and TEST2 have the same columns.  The negative side is that we have a need for the column orders to be identical.
     
    Finally, when running testing, we have automated test scripts that validate that the results in one environment match that of the results in a second environment.  This helps prove that the migration process is working and a regression test of functionality.  All validation is done with * in the selects so that when columns are added or removed, that the validation will detect the differences so that we can review and approve the new results.  This also makes column ordering important when comparing between environments.
     
    Hope this helps…
     
    Thanks,
    David
     
     
     
     
     
  • Follow / 19 Jul 2018 at 1:50pm

    Hi David,

    that helps, thanks.

    --

    Cheers,

    Norm. [TeamT]