Schema Compare for Oracle 4

Permissions required to use Schema Compare for Oracle

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 you are not the owner of the schemas you want to compare, the following privileges are required to compare objects:

  • 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.

A deployment script created from incomplete comparison results may fail or produce unexpected 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, ALTER INDEX
  • DROP <OBJECT TYPE> or DROP ANY <OBJECT TYPE> to drop objects
    For example, DROP TABLE
  • GRANT GRANT ANY OBJECT PRIVILEGE
  • If you have insufficient privileges to perform the actions in the deployment script, the script will fail.
  • To deploy using Schema Compare for Oracle, we recommended you have DBA privileges.
  • If you create a deployment script to run later, we recommended you run the script as the schema owner.

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:

dba_attribute_transformations

dba_clu_columns

dba_cluster_hash_expressions

dba_clustering_dimensions

dba_clustering_keys

dba_clustering_tables

dba_col_comments

dba_col_privs

dba_cons_columns

dba_cons_obj_columns

dba_constraints

dba_db_links

dba_dependencies

dba_du_columns

dba_external_locations

dba_external_tables

dba_ind_columns

dba_ind_partitions

dba_ind_subpartitions

dba_indexes

dba_indextype_comments

dba_indextype_operators

dba_indextypes

dba_join_ind_columns

dba_lob_partitions

dba_lob_subpartitions

dba_lobs

dba_log_group_columns

dba_log_groups

dba_mview_comments

dba_mview_logs

dba_mviews

dba_nested_table_cols

dba_nested_tables

dba_obj_colattrs

dba_objects

dba_opancillary

dba_oparguments

dba_opbindings

dba_operator_comments

dba_operators

dba_part_indexes

dba_part_key_columns

dba_part_tables

dba_queue_subscribers

dba_queue_tables

dba_queues

dba_refs

dba_role_privs

dba_sequences

dba_snapshots

dba_source

dba_subpart_key_columns

dba_subpartition_templates

dba_synonyms

dba_sys_privs

dba_tab_cols

dba_tab_comments

dba_tab_partitions

dba_tab_privs

dba_tab_subpartitions

dba_tables

dba_triggers

dba_types

dba_users

dba_varrays

dba_views

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:

all_users

m_owners

sys_ilm$

sys_ilmobjects

sys.objects$

sys.snap$

sys.sys_fba_period

sys.tab$

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.


Didn't find what you were looking for?