There’s a nasty rumor going around that you can’t compare database objects and/or code in Oracle SQL Developer. So let’s put that to bed right now. First, here’s how to compare:

So now that that’s settled, why don’t we take a look at how to compare a single table, to another table – whether it’s in the same database or a different database.

Database Diff

There’s no additional licensing requirement here. If you have SQL Developer, you can use this feature.

if you're going to compare 1 table to another, make sure you ONLY have 'tables' checked
if you’re going to compare 1 table to another, make sure you ONLY have ‘tables’ checked

And then, use this dialog to select your table(s):

Move over the object(s) you want to compare over to the right hand side.
Move over the object(s) you want to compare over to the right hand side.

And now we can move onto the results.

The differences, side-by-side, and the script to make B look like A

Common lines with differences are highlighted in blue, new lines are highlighted in red.
Common lines with differences are highlighted in blue, new lines are highlighted in red.

So that’s why they are different, but here’s the script to synch up the differences:

Read the script, TEST the script, apply the script.
Read the script, TEST the script, apply the script.

And that’s it. Well, that’s mostly it. If you have questions about how to compare a database object in a schema you don’t have the login information for, read this post next.

thatjeffsmith
Author

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

20 Comments

  1. Hi Jeff

    Is is still true that Schema Compare is free to use? I’ve been told it’s part of the Change Management pack but I’ve disabled that and I still don’t get a warning.

    Cheers

    Ptee

    • thatjeffsmith

      It’s part of the EM code for doing change management – BUT – we are allowed to offer this portion of functionality w/o incurring the license cost of the pack.

      Short answer: it’s free.

    • Hi Jeff

      Can I just check, is this licensing arrangement documented anywhere?

      I’d like to show it to a client if I can.

      Thanks

      Pete

  2. Pls tell me how can I compare only data of two table using tools in Oracle SQL Developer.

    • Feature request:
      Please add this. My dream is to compare the data in two tables with an identical schema, and have UPDATE or INSERT statements auto-generated same way this tool auto-generates ALTERs.

  3. SQLDeveloper does not allow you to do Database Diff on the same database.

    • thatjeffsmith

      Yeah it does. I demo this on a regular basis. Define two connections to your database, two different users.

    • Fernando

      Thank you Jeff.
      Sorry for not seeing that in the other comments.

  4. I am new to Oracle database, have worked before in MySQL and SQLSERVER, any good book or tutorial for suggestion.

    • thatjeffsmith

      I wouldn’t buy any books. When you get to a topic you need help with, try oraclebase.com – Tim has nice tutorials on almost all popular topics.

  5. How to get export complete comaprison script into single document?

    • thatjeffsmith

      With the objects that you want to synch checked, use the save button in the DIFF toolbar – that will save all of the scripts to one file.

  6. How do you compare two tables in two different schemas in the same database? When I select the same connection for the source and destination connections on step 1 of the Diff wizard and select Next the error message “Source and Destination are same Database nothing to Diff” is displayed.

  7. This is a really simple and easy to understand article. I have installed plsql developer and I am not sure how to compare objects in this tool. If you have any idea, can you please write a post on this. Meanwhile I will install sql developer and try this feature.

    • thatjeffsmith

      Sorry, I don’t work for Allround Automations and I can’t help you with PL/SQL Developer.

Write A Comment