0

I know I can compare two Oracle schemas by using Tools -> Database Diff in Oracle SQL Develop. It will open the following wizard:
enter image description here enter image description here
Which will compare everything you've checked.

The problem I'm currently facing is that I only want to compare the Schema (tables, constraints, etc.) itself. Everything except for the data inside the tables. All the data combined was more than 1 GB of INSERT INTO statements (which I couldn't even load into SQL Developer, so I had to use SqlPlus to insert it), and it's currently already running for 1 hour with the Database Diff without result.

Because the database I want to compare it to is empty anyway, I'm only interested in comparing the actual tables, instead of also the data inside the tables.
Is this possible with Oracle SQL Developer?

If not I guess I'll have to use an alternative to check if there are any differences. Either a different tool, or exporting both schemas without data and compare those two files (excluding timestamps and such), or duplicating the existing database with data - empty it - and then compare with Database Diff. There are work-arounds, but I'm just wondering if it's possible to use Database Diff without table-data.

PS: The reason why I want to check if they are the same: a colleague of mine created Liquibase scripts to create the database, and I want to check if everything is correct by comparing it to the database (with data) we already had.

  • Wouldn't it be easier to run liquibase with "updateSQL" against both database and compare the text file? if they are same, you shouldn't have any differences (except for change timestamp) ? Liquibase if that way pretty reliable, after maky years of using in prod, never had an issue of liquibase making a mistake. Also use expdp with content=metadata for both databases and compare, that will be a bit simpler too. plus you can keep it as a record. – Raj May 5 '17 at 12:58
2

SQL Developer's Database Diff doesn't compare the data in your tables, only the table definitions.

If you check 'Tables' it by default compare ALL tables.

However, if in the proceeding wizard dialog page, you specify one or more tables, it will ONLY compare those tables.

enter image description here

Your output report will look like this - note there is no mention of how rows may differ between any two tables or views.

enter image description here

  • I was about to delete my question when I realized that.. Came back from lunch, and the Database Diff was done (after little over 2 hours), and the only differences were indeed the tables without data which was what I wanted. It just took WAY longer than I was expecting, so I falsely assumed it included the data as well. So I'll accept your answer instead of deleting my question as reference to others. (And lol at the hr.BEER table in your pictures. xD) – Kevin Cruijssen May 5 '17 at 12:55
  • hr.BEER is definitely not covered in our HR policies :) – thatjeffsmith May 5 '17 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.