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

Basically run the Import data dictionary wizard twice, once for each schema.
Basically run the Import data dictionary wizard twice, once for each schema.

Save your design.

Compare/Merge Models

Not the same as the Sync to the Data Dictionary...not touching a database here.
Not the same as the Sync to the Data Dictionary…not touching a database here.

Select your design that you just saved…

make sure you get the 'source' right or you'll be dropping the wrong tables
make sure you get the ‘source’ right or you’ll be dropping the wrong tables

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.

Now we're cooking with gas!
Now we’re cooking with gas!

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.

TEST this code THEN run it
TEST this code THEN run it

Bonus: A Report!

So you can click a button on the compare/merge dialog for a summary report of the compare findings.

What's what?
What’s what?

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

bring stuff back up to speed with the database if needed, then do the model compare
bring stuff back up to speed with the database if needed, then do the model compare

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

6 Comments

  1. Mario De Luca Reply

    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.

  2. Mario De Luca Reply

    Trigger in the merge process between two Design model are not imported?

  3. Arjen van Eerde Reply

    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

  4. 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…

Reply To Arjen van Eerde Cancel Reply