Compare Schemas: Difference Details Tab - Detail View

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

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]

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

From: Norm [TeamT] [mailto:bounce-NormTeamT@toadworld.com]

Sent: Monday, July 16, 2018 3:11 AM

To: toadoracle@toadworld.com

Subject: [External Email] RE: [Toad for Oracle - Discussion Forum] Compare Schemas: Difference Details Tab - Detail View

EXTERNAL EMAIL

** RE: Compare Schemas: Difference Details Tab - Detail View**

Reply by Norm [TeamT]

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]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


***** This email is from an external sender outside of the CenterPoint Energy network. Be cautious about clicking links or opening attachments from unknown sources. *****


Hi David,

that helps, thanks.

There are pros and cons of the split screen diff details tab. Having it embedded is nice for a quick look, but full screen is nice too. I’ll look for a smooth way to achieve both.

Regarding rebuilding without dbms_redef: I get the feeling that you aren’t aware of this - It’s not exactly automated, but pretty close…you can right-click the table in the difference details and choose “Send to Rebuild Table window”. It will set up the window so that all you have to do is just Next,Next,Next through it to rebuild the table with the desired columns. This option should be enabled whenever there is column (add/remove/datatype) difference on a table, but I noticed if the only difference is column order, it is not enabled. I’ll fix that. I can look at fully automating this at some point in the future, but I can’t take it on right now.

Check next beta for a preview panel.

Thank you so much for adding the check box. It is working out very well.

Thanks,
David

1 Like