When comparing schemas in Toad, is there a way to have the program not drop and recreate a table in order for the target database to look like the reference database? I would much rather have “alter” statements to change the table structure as opposed to dropping the table (and the associated data, of course) and create the table. Is there a setting that I’m missing?
I’m using Toad for Oracle Base 10.6.1.3 w/ DB Admin Module.
Thanks.
No, there’s not, it’s either just simple add columns statements, or drop/recreate. The ‘sync’ script is not as configurable as it needs to be to truly manage and sync multiple schemas. Not meant as a rip, it is just one area that could use some attention.
Eh? The sync script will drop tables that don’t exist, and it’ll
create tables that don’t, but it does not do a drop and create of the same
object. If we can’t do it with an ALTER, we don’t do it. And if you
don’t want to drop tables that don’t exist, just check that section
in the results tab before you go to the sync script tab.
If you have an example of something being dropped and recreated, let me know how
to reproduce it and I’ll fix it.
Troy – if there is something specific you want to configure in sync
scripts that you can’t do, let me know about it.
I actually already forwarded a list of stuff to my rep as a post-mortem after I evaluated the sync script stuff using a trial license. But for starters, column order, column name changes, anything with partitions. Basically looking for some logic that preserves data like you get when you do a rebuild table, but also incorporating schema differences.
if you’d like to send me your list offline, I’ll be happy to go
through it.
Some of this stuff – column order, for example – is tricky and would
require use of dbms_redefinition, but it is doable. I’m not sure how I
could detect a column name change, but If I could, column renames can be done
without loss of data. We do make some partitioning changes in the sync script
when it doesn’t affect data, but like column reorder, some of that would
require dbms_redefinition.
This would be a good thing for idea pond. I’d be interested in knowing how
many users are interested in this. Most of it is doable, but it’s not a
simple change.
I’m struggling with the exact same issue. for example, i have added an index to one of the columns, something that should have been summed as something like:
ALTER TABLE tablename
ADD INDEX index_name (column_name);
And instead… it wants me to do the whole “create temp table, copy data to temp table, drop original table,rename temp table to original table name”… that’s crazy and moreover, it might (and will) lead to some downtime when trying to update a production db.
Is there a solution for this? Thanks…
The Schema Compare in Toad for Oracle does NOT do some crazy “create temp table, copy data to temp table, rebuild original table, copy data back” sequence. There is a window in Toad called “Rebuild Table” which does that. Maybe that’s what you are thinking of. Or maybe you are not using Toad for Oracle, but Toad for some other database, because your command above to create the index does not look very “Oracle-y”
If the only difference between the two schemas is the addition of an index, then Toad’s schema compare will just create the index. From the main menu: Database -> Compare -> Schemas.
Sorry, i entered his thread from google. wasn’t aware that it was referring to the Oracle version. In toad for MYSQL it behaves as i stated. Could you anyway comment on the same question just referring to the mysql version?
Thanks again
No problem. I can’t comment on Toad for MySQL. They have different developers and a different forum. Go here: www.toadworld.com/…/56.aspx
Oops, I think I sent you to the wrong place. Go here instead: www.toadworld.com/…/default.aspx
Sorry about that!
Yeah i noticed. In case anyone else will be looking for the mysql version, i opened a thread for it over there www.toadworld.com/…/24483.aspx