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.
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.
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:
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.
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.
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:
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!