SQL Developer Database Diff – Compare Objects From Multiple Schemas

thatjeffsmith SQL Developer 70 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

Tell Others About This Story:

Comments 70

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

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

    1. thatjeffsmith Post
      Author
  5. Hi Jeff,
    I’m on version 4.1.3.20, really looking forward to being able to select all the objects in Database Diff into a report. When do you think this capability will be available?

    1. thatjeffsmith Post
      Author
  6. Compare function hangs after clicking on the object with difference. So I can’t see what the difference are. Does anyone else experience similar problem?

  7. Hi Jeff,

    I need to migrate the database from MySQL to Oracle. I am using SQL developer for the migration. Could you suggest me is there any document or tool that will help for the migration.

    Thanks
    Kishore Basani

    1. thatjeffsmith Post
      Author
  8. Hi Jeff,

    I got the following errors while comparing two schemas from different databases
    Unable to compare objects.
    Ensure that you have the required permissions. 
    You must own the objects or have select_catalog_role

    I have granted the select_catalog_role to the user but still i got the same error message please help me with this.

    Thanks,
    Srikar

    1. thatjeffsmith Post
      Author
      1. I just want to know what are the privileges required for a regular db user(NON DBA) to run the database diff option.

        Thanks,
        Srikar

        1. thatjeffsmith Post
          Author
          1. I have granted select_catalog_role and select any dictionary to the schema still i see the same error . Could you please list the privileges required.

            Thanks,
            Srikar

          2. Jeff,
            I would like more information that would help me (developer) run Database Diff without errors. My DBA gave me catalog rights, but they didn’t like it. I still cannot run the utility.
            john3

          3. thatjeffsmith Post
            Author
  9. When will we have option to generate/export a diff report .. I notice the post was done in 2012

    Any updates?

  10. hi sir , i have one doubt sir

    i create two database tables A,B .THEN A table filed aa_type,aa_head,aa_desc,B table filed ai_type,ai_head,ai_desc,ai_slno

    i match the two table
    select aa_desc,ai_desc,ai_slno from A,B WHERE aa_type=ai_type ,aa_head=ai_head order by ai_slno;

    i put this query but same data repeated output,so please help for me
    how to clear my query

    1. thatjeffsmith Post
      Author
  11. Hi Jeff,

    Can we automate this Database Diff – Compare job, i want this to run every month once.
    And it should be automated, is it possible using sql developer..?
    Waiting for your reply.

    Thanks and Regards,
    Hemanth

  12. Thanks, Jeff! That makes perfect sense. I have lots of memory on my PC, but very little memory is available in the two databases that I’m running the schema compares in.

  13. Hi Jeff,
    I’m comparing several different softwares for schema comparison/sync, and it takes 2 hours in SQL Developer to do a database diff schema compare on my schema, compared with 5 minutes in Red-Gate Schema compare and 10 minutes in Toad. Why is SQL Developer (4.1) Database Diff so slow compared to the competitors?

    1. thatjeffsmith Post
      Author

      I can’t say why, but i can tell you how we’re different than those other tools.

      They mine the data dictionary and do the compares client side.

      We make database calls to dbms metadata package to get the object info, and then use some enterprise manager change management code to generate the report and sync code, all for free.

  14. Hi

    I am using sql developer to compare 2 databases. The problem is, on one database the table names have underscores in it and on the other theres no underscores. How can I overcome this by using database diff?

    1. thatjeffsmith Post
      Author

      I think that’s probably a showstopper for db diff.

      So what i would do instead, import both schemas to separate Data Modeler relational models. Then rename the underscore tables, then do a data model compare. It will also produce an ALTER script.

  15. Hi Jeff,
    I have a problem with ‘Package Compare’.
    Always, if I change the source of a package (1MB) a Task will be started and compare the package with any other packages (We have 6 Databases with the same Package but one DB will be my TestDB !).
    It’s take a lot of time if the task will be end (5-6 hours).

    I see no preferences to stop this feature !
    Version 4.1.0.18
    Build MAIN-18.37

    Is there any solution for my problem ?
    Thanks
    Karl

  16. hi Jeff,
    I never used SQL*DEV before, I like command prompt.
    but I must say this is really cool feature to find the DB objects differences.
    I could able to get the desired result.
    one question, I have got the report but when I closed and restarted SQL*DEV I couldn’t able to see that report though I selected “save all” option. Do I need to run the report again ?
    is there any feature to get that report exported in some kind of file or be there when I restart the Sql*Dev ?

    thanks
    Sachin

    1. thatjeffsmith Post
      Author

      I think what you must have saved is the diff alter scripts? We don’t have a report available to show all of the differences outside of the GUI itself.

      If you like command lines, I think you’ll really like our new SQLcl – have you seen my posts on that yet?

      1. hi Jeff,
        thanks for quick reply.

        I talking about the “Diff Report” tab ( which is clearly shown in picture you posted above).

        how to save this tab so I dont have to rerun again if restart the sql*dev ?

        thanks
        sachin.

        1. thatjeffsmith Post
          Author

          Check the objects you want the ALTER Scripts saved for, and hit the Save button. This will generate a .SQL file for you to run your script. There’s no report available for offline viewing of the differences themselves.

  17. I’m on version 4.0.1.14, really looking forward to being able to select all the objects in Database Diff into a report. When do you think this capability will be available?

    1. thatjeffsmith Post
      Author
  18. Hi Jeff,

    Great Blog.

    I looked across the comments and it is good to hear that there will be an exportable version of “Diff Report” generated. Looking forward to it
    An additional wishlist, will it be possible to also show the timings it took for comparison for each object_type. And also right now it seems to me that the comparison ob db objects is happening sequentially. May be the development team can have comparison of different object types run in parallel threads and finally consolidate. While trying a sample diff b/w my production and non-prod, it took close to 2 and half hours to do comparison for 2600 objects. You might consider it as an area of improvement

    Thanks,
    BS

  19. Hello. I wish to compare the structure of tables in two different schemas in two different databases. I can see all of the tables as I have the grants to them, but I do not login as those users. I can select them from the relevant schema in the Source part of the schema diff. process but I cannot select the right targets from the Destination. Am I missing something ? Thanks.

    1. thatjeffsmith Post
      Author
      1. Thanks for the speedy response. I tried that, but I could not see how to specify that the destination schema was not my own, i.e. not the username used by the destination connection.

        1. thatjeffsmith Post
          Author
          1. Hi Jeff. Thanks for that. The only problem in my setup is that the schema in the destination database is not the same as the one in the source. Enhancement request ?

          2. thatjeffsmith Post
            Author
  20. We have switched to PROXY connections to a common schema so we can separately authenticate. All is well in SQL Developer, except when I perform an export. I only get a small fraction of the schema-owned objects when connected as PROXY.

    I recognize that we are stuck using an older version 3.1.0.7, but wondering if this has been corrected in later versions.

    Thanks in advance

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  21. Hello Jeff,
    Just want to know if i can compare data between 2 table, lets say table A and B have same record count but different values in it and vice versa
    Is it possible using Diff wizard in sql developer

    thanks

    1. thatjeffsmith Post
      Author
  22. But that only allows compare within the same schema name right? I have db1.schema1 as the source and have to compare to db2.schema2. Seem like I will have to get a login to the target schema directly to compare between schemas?

    1. thatjeffsmith Post
      Author

      Right, if you don’t have access to schema X in target B, but you DO have access to schema X in source A, you’re good.

      If you don’t have access to the schema in either source or target, you need to ask someone for access or have them run the compare for you.

    1. thatjeffsmith Post
      Author
  23. I must be missing something really simple. Because for the life of me, I can’t find anywhere to change the overall background color in the prefs. All I seem to be able to change is the background color behind text. When working with Twighlight, for example, the background color remains black, other than behind the text I’ve changed.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  24. This is nice. I will be great if there was an option to connect to target schema as one user and run diff against object owned by another user, which is typical scenario for production access

    1. thatjeffsmith Post
      Author

      Agreed. We’re looking to make improvements in this area, both in the UI and the underlying engine. It’s obviously an important feature and we’re committed to ‘getting it right.’

      Thanks for the feedback AM!

      1. I agree. When trying to do a diff against objects that I don’t have direct connection to but when I use the Other Users option and can see the objects would be nice. Just wondering if you are working on this yet and when we can expect it?

Leave a Reply

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