How do I use a schema not based on the connection for database diff feature?

Question

I want to run the SQL Developer database diff feature on schemas different than the schema associated with the SQL Developer connection (my userid).

I log in with my userid but want to use one of the production schema for the comparison. The DBAs control the credentials for the production schemas; my userid can see the production schema objects. We have multiple production databases and want to check for difference in the objects' DDL across the various production schemas.

I tried alter session set current_schema = prod_schema;. This did not work and the diff report is based on objects under my userid ( I have no objects under my userid in production).

I use the GUI for the "diff wizard" in SQL Developer. No code.

Desired results would list the differences for the production schemas. Actual results are 0 results since my userid in the production database does not have any objects.

Answer 1

With Jeff's reply, I see that for the SQL Developer Database Diff, in the step 3 of 4 screen dialog, need to select the "More..." option to enable the schema selector list box. I am using SQL Developer v 18.3.0.277 on Oracle 11r2.

Answer 2

login as SYSTEM (or some other user) for source

On the Objects type screen, select 'Tables'

On the SELECT objects dialog, navigate to the schema where you want to do the compare

Move ALL of the tables over for schema HR

So instead of comparing SYSTEM schema to the target, only those 7 or so tables in HR will be compared to schema connection in the target.

If you don't want to use the schema attached to the target connection, you can use this option

If you say 'Maintain' then the schema attached to the object in the source is used to identify the schema to look in to compare the objects in the destination.

I talk about this more here.