In the current issue of Oracle Magazine, I have an article that discusses how to copy and compare Oracle objects using Oracle SQL Developer. ‘Make the Easy Move‘ was written using Oracle SQL Developer v3.1. As you know, version 3.2 was released several weeks ago, and one of the significant changes included was an update to the Database Diff feature.
The short story:
Previous versions of SQL Developer required the login of the schema that owned the destination objects for the compare. So if you wanted to compare an APPS development to an APPS production installation, you would need the APPS login for prod.
As you can imagine, this was a non-starter for many of our users. So for version 3.2, the Database Diff interface has been enhanced to allow for comparisons sans the destination object owner login. Continue reading if you’d like to see how 3.1 works versus 3.2.
Version 3.1
Database Diff is accessible from the Tools menu. You supply connectivity information for the ‘Source’ and ‘Destination’ locations of the objects you want to compare.
You DO have the HR login for PROD, yes???
I want to compare a ‘HR’ user and its objects in a development environment to a corresponding ‘HR’ user in a production environment. To make this happen requires that I supply the ‘HR’ user and password for production. In version 3.1 if you do not have the password for prod, you’ll either:
- not perform the compare
- ask your DBA to run the compare for you
If you supply a connection for the target connection other than HR, we’ll assume the same objects are in the schema assigned to that connection. You’ll probably see something like this in the results:
Your objects won’t be there though, so…
The report shows that all of my tables are missing because SQL Developer isn’t looking in the ‘HR’ schema in the ‘Destination Connection.’
We recognized this wasn’t a tenable scenario for most of our users. So for version 3.2 we introduced the following change.
Version 3.2
Now when configuring a compare scenario, you can tell SQL Developer to ‘Maintain’ the schema across to the destination connection. So even though I have used a SYS connection to my beta environment, SQL Developer will maintain ‘HR’ as the context for identifying the objects and running the compare. Notice also that ‘Schema’ option for the DDL generation options is enforced when using the ‘Maintain’ method.
Generate schema simply says to generate the schema name on all DDL. It is required in order to use the comparison option for maintain schema (because you have to have the schema in order to maintain it). Having the schema name in the source objects allows us to find it in the destination database.
Choose ‘maintain’ if you want to compare objects in the same schema in the destination but don’t have the user login for that schema.
I don’t have the HR connection information for my production environment, but I do have a highly privileged user in prod, so I’ll use that connection instead. Since I used ‘maintain’ in the diff scenario, it will find my HR objects even though I’m not logging in as HR.
And here’s the comparison results I expect:
NOLOGGING, oh no!
Clear as mud?
To summarize, if you want to compare some objects in an environment you don’t have the login information for, use the ‘maintain’ option in version 3.2 of Oracle SQL Developer. We’re looking to make this an even simpler interface for future releases, so stay tuned!
Need More Convincing to Give SQL Developer a Try?
Because @martinberx says so!
Twitter
RSS
GooglePlus
Facebook
Sep 13, 2012 @ 08:53:27
Hi Jeff!
Do you know if it is possible to compare the records between two tables from source to destination database. e.g. I would like to know if values of one or more columns in the development database match with the values in the production database.
///Felix
Sep 13, 2012 @ 15:09:32
Not with the GUI. But you can use SQL to answer that question using a MINUS. SELECT * FROM … MINUS SELECT * FROM … – will show you records in SetA and not SETB. And if you union all that with B – A, you can see differences in both directions.
There are several 3rd party tools out there that automate this and put a nice graphical interface on it, but we do not offer this yet. You could probably build this with a report though.
Sep 20, 2012 @ 14:27:08
I’m very happy to see this. While I currently have connections for all of our production instances we are looking to move away from this so the credentials for our prod instances are only held by a more limited group.
Sep 20, 2012 @ 15:49:22
It’s going to get better too! Thanks for the feedback.
Oct 19, 2012 @ 13:04:36
Well, I got all excited, but this still doesn’t do what I or my team need. I need to compare objects in two different schemas on the same database. And either maintain or consolidate seems to want to only look at the schema I connected to as source.
Looks to me like you guys are making this a much harder task than it needs to be. It should be as simple as “Specify source connection and schema”, “Specify target connection and schema”, optionally specify a subset of objects to compare and how, run.
Forcing the schema to be the same as the connection in -either- case is the problem. It’s almost like you’re trying to code an IDE for people who don’t understand what a schema is…and all I can say is “please don’t do that.” Or if you must do that, let me turn it off and supply the intelligence myself.
Oct 19, 2012 @ 13:21:14
Well, from another article you posted, I see that you can specify a schema, but from the behavior I see, that appears to require the DBA role? Because the schema dropdown is grayed out, even though I have SELECT_CATALOG_ROLE. Is there some other permission required? Because the chance of my getting the DBA role granted is about the same as the chances of the sun going nova tomorrow.
Apr 24, 2013 @ 12:46:21
I recall Database Diff working in 3.1, but ever since I upgraded to 3.2, the objects are being compared but the Diff Report window never appears… Strange.
Apr 24, 2013 @ 13:01:20
That would be a bug, as you’re describing it. Please open an SR with MOS or start a thread on the Forums and we’ll see what we can do.