SQL Developer Database Diff – Compare Objects From Multiple Schemas

thatjeffsmith SQL Developer 95 Comments

Tell Others About This Story:

Ever wonder why Database Diff isn’t called Schema Diff? One reason is because SQL Developer actually allows you to select objects from more than one schema in the ‘Source’ connection for compares.

Simply use the ‘More’ dialog view and select as many tables from as many different schemas as you require

Now, before you get around to testing this – as you should never believe what I say, trust but verify – two things you need to know:

  1. I’m using SQL Developer version 3.2
  2. On the initial screen you need to use the ‘Maintain’ option

Maintain tells SQL Developer to use the schema designation in the source connection to find the same corresponding object in the destination schema.

Choose ‘maintain’ if you want to compare objects in the same schema in the destination but don’t have the user login for that schema.

So after you’ve selected your databases, your diff preferences, and your objects – you’re ready to perform the compare and review your results.

The DIFF Report

Notice the highlighted text, SQL Developer is ‘maintaining’ the Schema context from the two databases.

Short and sweet. That’s pretty much all there is to doing a compare with SQL Developer with multiple schemas involved.

You may have noticed in some posts lately that my editor screenshots had a ‘green screen’ look and feel to them.

What’s with the black background in your editors?

In the SQL Developer preferences, you can set your editor color schemes.

I started with the ‘Twilight’ scheme (team Jacob in case you’re wondering) and then customized it further by going with a default green font color. You could go pretty crazy in here, and I’m assuming 90% of you could care less and will just stick with the original. But for those of you who are particular about your IDE styling – go crazy!

SQL Developer Editor Display Preferences

Related Posts

Tell Others About This Story:

Comments 95

  1. Hi Jeff
    Is there a way to use database diff from a command? or from SQLcl ?
    and are you using DBMS_METADATA_DIFF?
    The guys here want to use it from the TFS.
    I told them to use sqldeveloper but they want some API to use from the TFS so it can be automated.
    Thanks

    1. thatjeffsmith Post
      Author

      from command line – no

      do we use dbms_metadata_diff – no

      we do use dbms_metadata to generate XML objects but then we pass that through some OEM code to do the diff

      we’re working on a replacement/upgrade for this entire work flow but it will take awhile to get there – stay tuned

  2. I am not seeing options to select Source and Destination databases on 1st screen of Diff Database. What am I missing to perform. I have connected to both the databases in object browser.
    Please help me.

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  3. We have multiple schemas within a database (Dev env), and there are times when we need to do a diff between the schemas on the same database, usually against a golden copy of the metadata, but there does not seem to be a way to do that. The Diff Wizard insists on having two different databases. Is there a way to do a diff within the same database that maybe I am overlooking? Security constraints make doing a diff between Development and another database a painful process.

    1. thatjeffsmith Post
      Author
    2. I’m trying to diff two different schemas in Dev, one a development schema and one a golden copy. “We have multiple schemas within a database (Dev env), and there are times when we need to do a diff between the schemas on the same database” So, I am trying to diff between DEVDB.schema01 and DEVDB.schema02. Diff Wizard does not appear to have an obvious way to do that. I’m hoping it is just something I am missing.

    3. thatjeffsmith Post
      Author
    4. thatjeffsmith Post
      Author
    5. Due to security constraints, running a diff against production is a time consuming process, so we keep a schema with a golden copy of the current production metadata in our Dev environment. If I understand correctly, I can’t do a diff between two different schemas in the same DB, which is fine; I just need to find another tool that will do that. Thank you for your time! Have a wonderful weekend!

  4. Using version 18-something.
    Have a database and copy in a second database and need to compare.
    There are a 42 schemas in the destination, now after about 45 minutes I succeeded to select all objects of the first 6 schemas. Really!

    And now I wonder how long it will last, to create the diff.

    So unfortunately, object selection is incredible slow.
    Even when selecting all objects in a small schema it lasts minutes to add them to the list.

    The diff itself was finished in the few minutes as I was writing this, so this is ok, but why does it last that long to select objects?

    1. thatjeffsmith Post
      Author

      42 or 6?

      It’s basically setup to do 1 to 1. What exactly are you needing to do?

      And yes, the schema compares are not fast. There’s both database code running via DBMS_METADATA, and then we take the results and run it through some OEM code to generate the DIFF reports and DDL scripts.

    2. Well, there are 42 schemas in total, and after the object selection of the first 6 schemas lastet that long, I stopped further selection and started the diff for the first 6 schemas.
      Then I started a second and a third diff, also with smaller schemas it is faster. The speed also depends on the object type. Two larger Packages may last longer the a dozen tables.

      With many schemas 1 to 1 comparison is not efficient. If it would be possible to simply select several or all but “Oracle maintained” schemas with all objects option, and start making all the work in background after starting the actual diff, that would be nice.

    3. thatjeffsmith Post
      Author

      I still don’t understand what you’re doing.

      6 schemas.

      You’re picking 6 schemas, every object, in one database, to compare to 6 schemas, every object, in another database?

      We’re setup to do 1 to 1 comparisons, this post shows a variation, but it’s not meant to imply you should pick every object in more than several schemas to do a compare.

    4. “You’re picking 6 schemas, every object, in one database, to compare to 6 schemas, every object, in another database?”

      Yes, I was doing exactly that, and it lastet 45 minutes to add all the objects to the diff.

    5. thatjeffsmith Post
      Author
  5. In the Compare functionality, is there a way to keep matching lines lined up in the results? In other tools we have used, if there were lines in 1 object and not the other, it would show blank likes to keep matching code in sync.

    1. thatjeffsmith Post
      Author
  6. I ran a diff on 2 schema’s X and Y and when it was halfway though I clicked Run in Background (Hide), but Now I cannot see it on my screen and I don’t even know if it finished.

    1. thatjeffsmith Post
      Author
    2. Thanks it ran for about for about 4 hours, nothing showed up, and I ended up killing the sqldeveloper on my machine and then ran it on server, but thanks for responding quickly.

  7. How do I save the output from the ‘diff report’. It shows on my screen but , then what do I do with it? I need to send it to the director that requested it.

    1. thatjeffsmith Post
      Author

      You can save the diff scripts by checking the ones you approve and using the save button on the toolbar. There’s no diff report per se that you can save.

    2. Hi,
      The UX has been a biggest issue with SQL Developer. I run a compare tool and it gave me a list of objects, now I need to go though all objects (approximately 70) and scroll to right and then to left to see the whole DDL script. Why not add a wrap text to the result? Or add export to text, so I could use more friendly text editors?

    3. thatjeffsmith Post
      Author
    4. The best scenario for Oracle would be to buy tool from other vendor, e.g. Intellij. Trying to develop some tool is a waste of time, you would never do something like IntelliJ.

    5. thatjeffsmith Post
      Author
  8. The schema & Table comparison works but well. The DDL tab provides the differences between source and target table. The script tab provides the actual script to use to make the objects same.
    This is where it goes wrong.
    The script generated fails to consider the sequence of columns from the source and haphazardly provides the ALTER statements in a random order.
    I am using sql developer 4.1.1.19

    1. thatjeffsmith Post
      Author
  9. Hi Jeff,
    Scenario: I am logged in scott in both databases but i want to compare the HR schemas. scott has the select any table , select any dictionary,catalog_role roles.
    Can i do compare for HR schemas using sql developer?

    1. thatjeffsmith Post
      Author

      That might not be enough privs, but you can try:

      define connections, SCOTT to each database.

      set DDL Comparison Options, Schema, to ‘Maintain’

      select objects from HR schema in source.

      Maintain tells SQLDev to use the schema attached to the object in the source to ID the schema of the object to compare it TO in the target. Since they’re both ‘HR’, you should be good to go.

Leave a Reply

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