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.
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:
- I’m using SQL Developer version 3.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.
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
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!
103 Comments
Hi,
Nice Article.
How would I compare my database With client side database in Oracle?
Thanks in advance
The compare works the same no matter where the database is.
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?
Probably not for v4.2.
Thx for your reply.
Can we automate this Database Diff – Compare job?
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?
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
SQL Developer has a complete 3rd party migration project feature you can use.
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
And?
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
fairly high, esp if you don’t own the objects – try the DBMS_METADATA package spec comments/DOCs
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
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
What errors?
What didn’t they like?
Sorry I forgot to come back and report. My DBA got me in with select_catlog_role and all is well. Love SQL Developer and your blog!
john3
When will we have option to generate/export a diff report .. I notice the post was done in 2012
Any updates?
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
I don’t have the bandwidth to help people with their SQL here. Try the SQL OTN Forum.
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
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.
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?
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.
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?
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.
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
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
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?
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.
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.
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?
Not sure, but it’s definitely still on our list of things to get to.
Really good post!
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
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.
Use the ‘maintain’ option to have the schema defined by the source used to define the schema in the target.
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.
you specify it by picking the schema in the source connection – that schema is then used in the destination connection to find the object, no matter what user you log in with
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 ?
Then you’ll need someone with access to the destination schema login to do the compare for you.
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
How do we export our Diff Report to Excel, now that we have run it? I want to be able to share what I am seeing with the rest of my team.
We don’t have a report for this feature yet – but it’s coming.
I’m looking at the same issue and there is no way to export this data?
You can export the scripts. Check the items you want included, then hit the ‘Save’ button.
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
Not via diff wizard, but you could run some MINUS queries to see what records are missing from SET A and B…
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?
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.
So has this changed in 4.0? I need to compare to a different target schema on a different database but do not have a direct login to the target.
No changes in 4, you’ll need to use the ‘Maintain’ option to compare objects in schemas you don’t have the Login/Password for in the Destination Connection.
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.
What do you mean by overall John? Could you do up a screenshot? I think that would clear things up for me.
Sorry for the SLOW reply, Jeff! Take a look at the dark gray background behind text, but the black background overall. Can’t figure how to get that black to also become dark gray…
https://plus.google.com/photos/100434438098942963048/albums/5832294952419403825/5832294954664512578
Ahhh, now I get it.
I don’t see a way at first and second glance to achieve that effect.
db comparison doesn’t support TimesTen? I only can see oracle connections in the drop down list.
The database comparison is happening via some Oracle PL/SQL packages, so yes, it’s only supported for Oracle Database compares.
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
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!
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?