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 :)](https://www.thatjeffsmith.com/wp-content/uploads/2014/09/plan_compare1.png)
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.](https://www.thatjeffsmith.com/wp-content/uploads/2014/09/compare_tables1.png)
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.](https://www.thatjeffsmith.com/wp-content/uploads/2014/06/single_compare3.png)
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.](https://www.thatjeffsmith.com/wp-content/uploads/2013/10/compare31.png)
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)](https://www.thatjeffsmith.com/wp-content/uploads/2012/03/pending3.png)
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.
2 Comments
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
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…