Selecting schemas to compare

On the Project Configuration dialog box, all schemas in a database are available to select from the Schemas list:

You don't require any privileges to select schemas for comparison.

Comparing schemas

If possible, the owner of the schema has all the privileges needed to compare everything. 

If you are not the owner of the schemas you want to compare, the following privileges are required to compare objects:

• Ideally system or sysdba privileges provide the best option as provides access to all the dba_dictionaries/views.
• If system or sysdba user is not an option, the SELECT_CATALOG_ROLE provides all the permissions needed for the comparison and does not grant any access to the data, so very secure.
• SELECT ANY DICTIONARY permissions, similar to SELECT_CATALOG_ROLE.  However in testing we found being granted the SELECT_CATALOG_ROLE the better option. 
• Non-PL/SQL objects (for example TABLES, VIEWS, SYNONYMS) require the SELECT privilege
• PL/SQL objects (for example PROCEDURES, FUNCTIONS, PACKAGES) require the EXECUTE privilege

If you have insufficient privileges to compare an object, it isn't displayed in the comparison results.

Deployment

To run a deployment script created by Schema Compare for Oracle, the following system privileges may be required:

• ALTER <OBJECT TYPE> or ALTER ANY <OBJECT TYPE> to alter objects
  For example, ALTER ANY PROCEDURE
• CREATE <OBJECT TYPE> or CREATE ANY <OBJECT TYPE> to create objects
  For example, CREATE INDEX
• DROP <OBJECT TYPE> or DROP ANY <OBJECT TYPE> to drop objects
  For example, DROP TABLE
• GRANT GRANT ANY OBJECT PRIVILEGE


Further Information

Users may experience an Oracle error ORA-00942 when attempting to perform a comparison that informs that a Table or View does not exist.  This may indicate that the user account specified to connect to the Oracle database does not have security permissions to read the Oracle Data Dictionary and other system objects.

When performing a comparison, the comparison engine queries Oracle Data Dictionaries.  It will first attempt to connect using dba_<data dictionary name>, for example dba_tables.  If this fails, the comparison engine automatically switches to use the all_<data dictionary name>, for example all_tables.

Below is a list off all data dictionary views (or their all_ equivalents) that the comparison engine requires security privileges to (grouped by type, to be able to compare for example Tables comparison engine needs access to more "dba_" dictionary views than just "dba_tables"):

Table

dba_tables
dba_mviews
dba_mview_logs
dba_part_tables
dba_external_tables
dba_external_locations

Columns permissions
Indexes permissions

If option "lobs and varrays" is selected:

dba_lob_subpartitions
dba_lob_partitions
dba_lobs

If option "tablespace and partitioning" is selected:

dba_ind_partitions
dba_ind_subpartitions
dba_tab_partitions
dba_tab_subpartitions
dba_part_key_columns
dba_subpart_key_columns
dba_subpartition_templates

If clustering is available and database version is 12c or higher:

dba_clustering_tables
dba_clustering_keys
dba_clustering_dimensions
dba_clustering_joins

If queues tables are available:

dba_queue_tables
dba_queue_subscribers
dba_queues

If database version is 9i:

dba_tab_comments

Column

dba_tab_cols
dba_nested_tables
dba_varrays
dba_types
dba_col_comments

If identity columns are available and database version is 12c or higher :

dba_sequences
dba_tab_identity_cols
dba_users

If database version is 9i:

dba_obj_colattrs
dba_cons_obj_columns

Index

dba_indexes
dba_ind_columns
dba_part_indexes
dba_constraints
dba_refs
dba_cons_columns

Tables permissions
Columns permissions

Index types

dba_indextypes
dba_indextype_operators
dba_indextype_comments

Index joined columns

dba_join_ind_columns

Dependencies

dba_dependencies

View

dba_views
dba_objects
dba_tab_comments

Tables permissions
Columns permissions
Indexes permissions

Materialized View

dba_snapshots
dba_mview_comments

Tables permissions
Columns permissions
Indexes permissions

Materialized View Logs

dba_mview_logs

Tables permissions
Columns permissions
Indexes permissions

Type

dba_objects
dba_types
dba_source

PL/SQL objects (function, procedure, package, package body)

dba_objects
dba_source

Trigger

dba_triggers
dba_objects
dba_source

Sequence

dba_sequences

Synonym and public synonym

dba_synonyms
dba_objects

Object privileges

dba_tab_privs
dba_objects
dba_col_privs

Role

dba_role_privs

System privileges

dba_sys_privs

Cluster

dba_clusters
dba_cluster_hash_expressions
dba_clu_columns

Database links

dba_db_links

Operator

dba_operators
dba_opbindings
dba_oparguments
dba_opancillary
dba_operator_comments

Transformation

dba_attribute_transformations

Log group

dba_log_groups
dba_log_group_columns

The comparison engine will query the following system tables, for example the sys.snap$ table is used to obtain information on the Materialized Views Reduced Precisions:

If after granting select on all_objects to your user or signing in as a different (perhaps dba) user you still get the same problem you may have to check your user’s access to each of these.