Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

30 SQL Developer Tips in 30 Days, Day 10: Compare Stuff Side By Side

2 Mins Read

We try to make comparing things easy to do. When you’re troubleshooting, you frequently need to know why A isn’t quite like B.

So let’s look at a few scenarios.

Execution Plans or Autotrace Runs

Run a plan or autotrace. Pin it. Run another. Right-click on one, and compare with the other. Voila.

Partitioning makes all the difference :)
Partitioning makes all the difference 🙂

Looking at Objects, Side By Side

So what’s the difference between those two hockey stat tables?

Open a table. Pin it. Open the second table. Pin it. Right click on the 2nd table editor tab, and say ‘New Document Tab Group.’

Two table editors, side-by-side. One has some nice partitions, the other has none.
Two table editors, side-by-side. One has some nice partitions, the other has none.

Show me table definitions, or any OTHER object.

Tools > Database Diff. Pick A, Pick B, select the object or objects you want compared.

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.

But I want to compare programs or queries!

Open program or query 1. File > Compare with. Bingo.

The code, side by side, again with difference highlighted.
The code, side by side, again with difference highlighted.

Need more help or detailed instructions?

I have detailed posts on how to do all of these compares in SQL Developer. Just use the search panel on your right to find what you’re looking for.

Bonus: Comparing Data Models!

Yeah, we can do that too.

I've changed the column from a CLOB to a VARCHAR(4000)
I’ve changed the column from a CLOB to a VARCHAR(4000)

You can even compare the diagrams – you’ll see how the coordinate system mapping points are different…just in case that’s really important to you.

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.

Related Posts

2 Comments

  1. Reina Reply

    Hi Jeff,

    I am a QA Specialist and my current project is to compare source and target tables (the data quality not the structure)
    using SQL queries in SQL Developer.

    I am not a DBA, so I need some simple queries on how to make sure Table A when it migrated to Table B has all the correct data on it using a simple query.

    Table A resides on the Acceptance schema (schema name = SOURCE)
    Table B now resides on the Acceptance Schema (schema name = TARGETSTG)

    My goal was to create SQL queries from Source and then export it in EXCEL. Then create sql query in it’s corresponding staging table and export it to EXCEL.
    From SourceExcel, I compare targetExcel.

    Do you have any idea on how I can make this possible. I worked with 40++ tables so I need a fast way to go about it.

    Thanks in advance.
    Reina

    • thatjeffsmith Post Author

      This only makes sense if you have a small number of tables, those tables are small, and you don’t mind a lot of manual work.

      I’ve yet to see a good data compare engine that scales and has a great UI. Great UI is easy if you have a small amount of data. Asking the client to compare a few thousand rows is no big deal. Asking it to compare billions of bits – not so much.

      You could use MINUS queries over DB_LINK – but bringing in a DB_LINK into the equation isn’t going to be fun either.

      What are you using to copy/synch the data in the first place? Some data replication technologies will tell you if the data isn’t synched up…

Write A Comment