A question came up on the oracle-l…
I am working in SQL developer 4.0. I am comparing my acceptance version of a schema to my production version of the same schema. We did some ‘clean-up’ of old tables and views in acceptance. We want to apply the same changes to production, when we go to prod. How do I tell SQL D to produce a ‘drop’ statement when running a diff and doing the compare?
Our DIFF feature won’t kick tables out of a schema just because they aren’t in the ‘source’ material. It will add missing tables, and fix ones that are ‘different,’ but it won’t drop the strangers.
However, there IS a way to get what you want here.
Enter the Data Modeler.
Import Your Target and Destination Schemas to Two New Relational Models in your Design
Save your design.
Compare/Merge Models
Select your design that you just saved…
Click ‘Ok.’
Now run the compare again?
It’s most likely you have a compare option set to consider the ‘schema.’
If you’re comparing two schemas in the same database, then you’ll need to disable the schema compare option – that is treat A.TABLE the same as B.TABLE.
If you’re in two different databases and the schemas are the SAME, then you’re good.
Onto the Results
Make sure they look right.
You should see some tables as ‘To Drop’ on the left. You’ll need to select those checkboxes. Then when you generate the DDL, you’ll get the DROP statements.
DROPping objects can be a big OUCH!, so we don’t do it by default.
Bonus: A Report!
So you can click a button on the compare/merge dialog for a summary report of the compare findings.
Remember, not the Live Database
So if the database has changed since you imported the two schemas to designs, you’ll need to use the Sync Model to Data Dictionary Dialog first to bring it up to speed.
That’s THIS button
6 Comments
Sorry i should have wrote this post in other place, it’s wrong for me write that here because i refer to Sql Data Modeler and the merge between two design model. i post same question in another forum.
Trigger in the merge process between two Design model are not imported?
Sorry, Mario, how do you mean? I don’t have any context for your request.
Hi Jeff,
I understand that setting the options or choosing the options is interactive, however those can be pre-set in a configuration / parameter file. It would be really great if the diff and generation of the corresponding update script can be scripted. We even think of building it ourselves, but as you probably know, that’s not a trivial task.
Arjen
Howdy Jeff, This is a great option! Thanks for sharing. Can this comparison feature be scripted? Many disconnected environments that should have the same database structures requires significant repeated efforts. Possibly where the script is callable from an APEX front-end as a verification of the environments!
Jeff
Not today, the output isn’t easily sent to a file as it’s interactive. You have to review the differences and decide what you want to accept…