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.
Select your design that you just saved…
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