Using the Modeler to Compare Two Schemas

thatjeffsmith SQL Developer 3 Comments

Tell Others About This Story:

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

Tell Others About This Story:

Comments 3

  1. 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

  2. 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

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *