9 Replies Latest reply: Jul 23, 2013 1:24 PM by sb92075 RSS

    How to compare two oracle database schemas

    487866
      hi all,
      i want to compare and find out differences between two oracle databases. The comparison should be made on between table definition,table data,primary and foreign keys, packages and functions.
      Is there any management console or tool embedded in the oracle enterprise manger?

      it is really appreciate if anyone can point me towards wright direction asap.
        • 1. Re: How to compare two oracle database schemas
          cd_2
          I can't say anything about OEM, but I recall that Toad had a menu option on comparing schemas. Another way would be a sql script, you may find this thread interesting:

          http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:941629680330

          C.
          • 2. Re: How to compare two oracle database schemas
            48387
            To compare objects (schemas),

            in TOAD you can do a "compare schema " (under DBA tab) and
            in OEM, you have to access Change Manager tool and compare database objects.

            TOAD is straightforward and doesn't require a repository to run. Good luck.
            • 3. Re: How to compare two oracle database schemas
              user486132
              Hi, You can compare two database schemas using PL/SQL Developer 5.0.3.527

              Options: Tools/Compare user objects

              Or using Oracle Change Manager
              • 4. Re: How to compare two oracle database schemas
                441662
                Not sure how to compare the data, but you can compare the schema using my "poor man's way to compare"...

                Do a select using these views.

                ALL_TABLES
                ALL_TAB_COLUMNS
                ALL_INDEXES
                ALL_IND_COLUMNS
                etc...

                Have the results go a text file.

                Do this for each schema.

                Then use a diff tool to compare the two files.
                • 5. Re: How to compare two oracle database schemas
                  489325
                  Hi ,

                  This script would help u a lot in comparing 2 schemas and generate a report on them.

                  Script:
                  -------
                  This script will compare two Oracle schemas and generate a report of
                  discrepencies and this script has been used against Oracle 7.3.4, 8.0.5,
                  REM and 8.1.7 databases, but it should also work with other versions.
                  REM
                  REM Please note that the following schema object types and attributes are
                  REM not compared by this script at this time:
                  REM
                  REM cluster definitions
                  REM comments on tables and columns
                  REM nesting, partition, IOT, and temporary attributes of tables
                  REM snapshots/materialized views, logs, and refresh groups
                  REM foreign function libraries
                  REM object types
                  REM operators
                  REM indextypes
                  REM dimensions
                  REM auditing information
                  REM new schema attributes added for Oracle 9i
                  REM
                  REM Version 02-04-2002
                  REM

                  PROMPT
                  PROMPT Schema Comparison
                  PROMPT =================
                  PROMPT
                  PROMPT Run this script while connected to one Oracle schema. Enter the Oracle
                  PROMPT username, password, and SQL*Net / Net8 service name of a second schema.
                  PROMPT This script will compare the two schemas and generate a report of
                  PROMPT differences.
                  PROMPT
                  PROMPT A temporary database link and table will be created and dropped by
                  PROMPT this script.
                  PROMPT

                  ACCEPT schema CHAR PROMPT "Enter username for remote schema: "
                  ACCEPT passwd CHAR PROMPT "Enter password for remote schema: " HIDE
                  ACCEPT tnssvc CHAR PROMPT "Enter SQL*Net / Net8 service for remote schema: "

                  PROMPT

                  ACCEPT report CHAR PROMPT "Enter filename for report output: "

                  SET FEEDBACK OFF
                  SET VERIFY OFF

                  CREATE DATABASE LINK rem_schema CONNECT TO &schema IDENTIFIED BY &passwd
                  USING '&tnssvc';

                  SET TRIMSPOOL ON

                  SPOOL &report

                  SELECT SUBSTR (RPAD (TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss'), 25), 1, 25)
                  "REPORT DATE AND TIME"
                  FROM SYS.dual;

                  COL local_schema FORMAT a35 TRUNC HEADING "LOCAL SCHEMA"
                  COL remote_schema FORMAT a35 TRUNC HEADING "REMOTE SCHEMA"

                  SELECT USER || '@' || C.global_name local_schema,
                  A.username || '@' || B.global_name remote_schema
                  FROM user_users@rem_schema A, global_name@rem_schema B, global_name C
                  WHERE ROWNUM = 1;

                  SET PAGESIZE 9999
                  SET LINESIZE 250
                  SET FEEDBACK 1

                  SET TERMOUT OFF

                  PROMPT

                  REM Object differences
                  REM ==================

                  COL object_name FORMAT a30

                  PROMPT SUMMARY OF OBJECTS MISSING FROM LOCAL SCHEMA

                  SELECT object_type, COUNT (*)
                  FROM
                  (
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name)
                  FROM user_objects@rem_schema
                  MINUS
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name)
                  FROM user_objects
                  )
                  GROUP BY object_type
                  ORDER BY object_type;

                  PROMPT SUMMARY OF EXTRANEOUS OBJECTS IN LOCAL SCHEMA

                  SELECT object_type, COUNT (*)
                  FROM
                  (
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name)
                  FROM user_objects
                  WHERE object_type != 'DATABASE LINK'
                  OR object_name NOT LIKE 'REM_SCHEMA.%'
                  MINUS
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name)
                  FROM user_objects@rem_schema
                  )
                  GROUP BY object_type
                  ORDER BY object_type;

                  PROMPT OBJECTS MISSING FROM LOCAL SCHEMA

                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name) object_name
                  FROM user_objects@rem_schema
                  MINUS
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name) object_name
                  FROM user_objects
                  ORDER BY object_type, object_name;

                  PROMPT EXTRANEOUS OBJECTS IN LOCAL SCHEMA

                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name) object_name
                  FROM user_objects
                  WHERE object_type != 'DATABASE LINK'
                  OR object_name NOT LIKE 'REM_SCHEMA.%'
                  MINUS
                  SELECT object_type,
                  DECODE (object_type,
                  'INDEX', DECODE (SUBSTR (object_name, 1, 5),
                  'SYS_C', 'SYS_C', object_name),
                  'LOB', DECODE (SUBSTR (object_name, 1, 7),
                  'SYS_LOB', 'SYS_LOB', object_name),
                  object_name) object_name
                  FROM user_objects@rem_schema
                  ORDER BY object_type, object_name;

                  PROMPT OBJECTS IN LOCAL SCHEMA THAT ARE NOT VALID

                  SELECT object_name, object_type, status
                  FROM user_objects
                  WHERE status != 'VALID'
                  ORDER BY object_name, object_type;

                  REM Table differences
                  REM =================

                  PROMPT TABLE COLUMNS MISSING FROM ONE SCHEMA
                  PROMPT (NOTE THAT THIS REPORT DOES NOT LIST DISCREPENCIES IN COLUMN ORDER)

                  (
                  SELECT table_name, column_name, 'Local' "MISSING IN SCHEMA"
                  FROM user_tab_columns@rem_schema
                  WHERE table_name IN
                  (
                  SELECT table_name
                  FROM user_tables
                  )
                  MINUS
                  SELECT table_name, column_name, 'Local' "MISSING IN SCHEMA"
                  FROM user_tab_columns
                  )
                  UNION ALL
                  (
                  SELECT table_name, column_name, 'Remote' "MISSING IN SCHEMA"
                  FROM user_tab_columns
                  WHERE table_name IN
                  (
                  SELECT table_name
                  FROM user_tables@rem_schema
                  )
                  MINUS
                  SELECT table_name, column_name, 'Remote' "MISSING IN SCHEMA"
                  FROM user_tab_columns@rem_schema
                  )
                  ORDER BY 1, 2;

                  COL schema FORMAT a15
                  COL nullable FORMAT a8
                  COL data_type FORMAT a9
                  COL data_length FORMAT 9999 HEADING LENGTH
                  COL data_precision FORMAT 9999 HEADING PRECISION
                  COL data_scale FORMAT 9999 HEADING SCALE
                  COL default_length FORMAT 9999 HEADING LENGTH_OF_DEFAULT_VALUE

                  PROMPT DATATYPE DISCREPENCIES FOR TABLE COLUMNS THAT EXIST IN BOTH SCHEMAS

                  (
                  SELECT table_name, column_name, 'Remote' schema,
                  nullable, data_type, data_length, data_precision, data_scale,
                  default_length
                  FROM user_tab_columns@rem_schema
                  WHERE (table_name, column_name) IN
                  (
                  SELECT table_name, column_name
                  FROM user_tab_columns
                  )
                  MINUS
                  SELECT table_name, column_name, 'Remote' schema,
                  nullable, data_type, data_length, data_precision, data_scale,
                  default_length
                  FROM user_tab_columns
                  )
                  UNION ALL
                  (
                  SELECT table_name, column_name, 'Local' schema,
                  nullable, data_type, data_length, data_precision, data_scale,
                  default_length
                  FROM user_tab_columns
                  WHERE (table_name, column_name) IN
                  (
                  SELECT table_name, column_name
                  FROM user_tab_columns@rem_schema
                  )
                  MINUS
                  SELECT table_name, column_name, 'Local' schema,
                  nullable, data_type, data_length, data_precision, data_scale,
                  default_length
                  FROM user_tab_columns@rem_schema
                  )
                  ORDER BY 1, 2, 3;

                  REM Index differences
                  REM =================

                  COL column_position FORMAT 999 HEADING ORDER

                  PROMPT INDEX DISCREPENCIES FOR INDEXES THAT EXIST IN BOTH SCHEMAS

                  (
                  SELECT A.index_name, 'Remote' schema, A.uniqueness, A.table_name,
                  B.column_name, B.column_position
                  FROM user_indexes@rem_schema A, user_ind_columns@rem_schema B
                  WHERE A.index_name IN
                  (
                  SELECT index_name
                  FROM user_indexes
                  )
                  AND B.index_name = A.index_name
                  AND B.table_name = A.table_name
                  MINUS
                  SELECT A.index_name, 'Remote' schema, A.uniqueness, A.table_name,
                  B.column_name, B.column_position
                  FROM user_indexes A, user_ind_columns B
                  WHERE B.index_name = A.index_name
                  AND B.table_name = A.table_name
                  )
                  UNION ALL
                  (
                  SELECT A.index_name, 'Local' schema, A.uniqueness, A.table_name,
                  B.column_name, B.column_position
                  FROM user_indexes A, user_ind_columns B
                  WHERE A.index_name IN
                  (
                  SELECT index_name
                  FROM user_indexes@rem_schema
                  )
                  AND B.index_name = A.index_name
                  AND B.table_name = A.table_name
                  MINUS
                  SELECT A.index_name, 'Local' schema, A.uniqueness, A.table_name,
                  B.column_name, B.column_position
                  FROM user_indexes@rem_schema A, user_ind_columns@rem_schema B
                  WHERE B.index_name = A.index_name
                  AND B.table_name = A.table_name
                  )
                  ORDER BY 1, 2, 6;

                  REM Constraint differences
                  REM ======================

                  PROMPT CONSTRAINT DISCREPENCIES FOR TABLES THAT EXIST IN BOTH SCHEMAS

                  SET FEEDBACK OFF

                  CREATE TABLE temp_schema_compare
                  (
                  database NUMBER(1),
                  object_name VARCHAR2(30),
                  object_text VARCHAR2(2000),
                  hash_value NUMBER
                  );

                  DECLARE
                  CURSOR c1 IS
                  SELECT constraint_name, search_condition
                  FROM user_constraints
                  WHERE search_condition IS NOT NULL;
                  CURSOR c2 IS
                  SELECT constraint_name, search_condition
                  FROM user_constraints@rem_schema
                  WHERE search_condition IS NOT NULL;
                  v_constraint_name VARCHAR2(30);
                  v_search_condition VARCHAR2(32767);
                  BEGIN
                  OPEN c1;
                  LOOP
                  FETCH c1 INTO v_constraint_name, v_search_condition;
                  EXIT WHEN c1%NOTFOUND;
                  v_search_condition := SUBSTR (v_search_condition, 1, 2000);
                  INSERT INTO temp_schema_compare
                  (
                  database, object_name, object_text
                  )
                  VALUES
                  (
                  1, v_constraint_name, v_search_condition
                  );
                  END LOOP;
                  CLOSE c1;
                  OPEN c2;
                  LOOP
                  FETCH c2 INTO v_constraint_name, v_search_condition;
                  EXIT WHEN c2%NOTFOUND;
                  v_search_condition := SUBSTR (v_search_condition, 1, 2000);
                  INSERT INTO temp_schema_compare
                  (
                  database, object_name, object_text
                  )
                  VALUES
                  (
                  2, v_constraint_name, v_search_condition
                  );
                  END LOOP;
                  CLOSE c2;
                  COMMIT;
                  END;
                  /

                  SET FEEDBACK 1

                  (
                  SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
                  '9', NULL) constraint_name,
                  'Remote' schema, A.constraint_type, A.table_name,
                  A.r_constraint_name, A.delete_rule, A.status, B.object_text
                  FROM user_constraints@rem_schema A, temp_schema_compare B
                  WHERE A.table_name IN
                  (
                  SELECT table_name
                  FROM user_tables
                  )
                  AND B.database (+) = 2
                  AND B.object_name (+) = A.constraint_name
                  MINUS
                  SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
                  '9', NULL) constraint_name,
                  'Remote' schema, A.constraint_type, A.table_name,
                  A.r_constraint_name, A.delete_rule, A.status, B.object_text
                  FROM user_constraints A, temp_schema_compare B
                  WHERE B.database (+) = 1
                  AND B.object_name (+) = A.constraint_name
                  )
                  UNION ALL
                  (
                  SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
                  '9', NULL) constraint_name,
                  'Local' schema, A.constraint_type, A.table_name,
                  A.r_constraint_name, A.delete_rule, A.status, B.object_text
                  FROM user_constraints A, temp_schema_compare B
                  WHERE A.table_name IN
                  (
                  SELECT table_name
                  FROM user_tables@rem_schema
                  )
                  AND B.database (+) = 1
                  AND B.object_name (+) = A.constraint_name
                  MINUS
                  SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
                  '9', NULL) constraint_name,
                  'Local' schema, A.constraint_type, A.table_name,
                  A.r_constraint_name, A.delete_rule, A.status, B.object_text
                  FROM user_constraints@rem_schema A, temp_schema_compare B
                  WHERE B.database (+) = 2
                  AND B.object_name (+) = A.constraint_name
                  )
                  ORDER BY 1, 4, 2;

                  REM Database link differences
                  REM =========================

                  PROMPT DATABASE LINK DISCREPENCIES

                  COL db_link FORMAT a40

                  (
                  SELECT db_link, 'Remote' schema, username, host
                  FROM user_db_links@rem_schema
                  MINUS
                  SELECT db_link, 'Remote' schema, username, host
                  FROM user_db_links
                  )
                  UNION ALL
                  (
                  SELECT db_link, 'Local' schema, username, host
                  FROM user_db_links
                  WHERE db_link NOT LIKE 'REM_SCHEMA.%'
                  MINUS
                  SELECT db_link, 'Local' schema, username, host
                  FROM user_db_links@rem_schema
                  )
                  ORDER BY 1, 2;

                  REM Sequence differences
                  REM ====================

                  PROMPT SEQUENCE DISCREPENCIES

                  (
                  SELECT sequence_name, 'Remote' schema, min_value, max_value,
                  increment_by, cycle_flag, order_flag, cache_size
                  FROM user_sequences@rem_schema
                  MINUS
                  SELECT sequence_name, 'Remote' schema, min_value, max_value,
                  increment_by, cycle_flag, order_flag, cache_size
                  FROM user_sequences
                  )
                  UNION ALL
                  (
                  SELECT sequence_name, 'Local' schema, min_value, max_value,
                  increment_by, cycle_flag, order_flag, cache_size
                  FROM user_sequences
                  MINUS
                  SELECT sequence_name, 'Local' schema, min_value, max_value,
                  increment_by, cycle_flag, order_flag, cache_size
                  FROM user_sequences@rem_schema
                  )
                  ORDER BY 1, 2;

                  REM Private synonym differences
                  REM ===========================

                  PROMPT PRIVATE SYNONYM DISCREPENCIES

                  (
                  SELECT synonym_name, 'Remote' schema, table_owner, table_name, db_link
                  FROM user_synonyms@rem_schema
                  MINUS
                  SELECT synonym_name, 'Remote' schema, table_owner, table_name, db_link
                  FROM user_synonyms
                  )
                  UNION ALL
                  (
                  SELECT synonym_name, 'Local' schema, table_owner, table_name, db_link
                  FROM user_synonyms
                  MINUS
                  SELECT synonym_name, 'Local' schema, table_owner, table_name, db_link
                  FROM user_synonyms@rem_schema
                  )
                  ORDER BY 1, 2;

                  REM PL/SQL differences
                  REM ==================

                  PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
                  PROMPT THAT EXIST IN BOTH SCHEMAS

                  SELECT name, type, COUNT (*) discrepencies
                  FROM
                  (
                  (
                  SELECT name, type, line, text
                  FROM user_source@rem_schema
                  WHERE (name, type) IN
                  (
                  SELECT object_name, object_type
                  FROM user_objects
                  )
                  MINUS
                  SELECT name, type, line, text
                  FROM user_source
                  )
                  UNION ALL
                  (
                  SELECT name, type, line, text
                  FROM user_source
                  WHERE (name, type) IN
                  (
                  SELECT object_name, object_type
                  FROM user_objects@rem_schema
                  )
                  MINUS
                  SELECT name, type, line, text
                  FROM user_source@rem_schema
                  )
                  )
                  GROUP BY name, type
                  ORDER BY name, type;

                  PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
                  PROMPT THAT EXIST IN BOTH SCHEMAS (CASE INSENSITIVE COMPARISON)

                  SELECT name, type, COUNT (*) discrepencies
                  FROM
                  (
                  (
                  SELECT name, type, line, UPPER (text)
                  FROM user_source@rem_schema
                  WHERE (name, type) IN
                  (
                  SELECT object_name, object_type
                  FROM user_objects
                  )
                  MINUS
                  SELECT name, type, line, UPPER (text)
                  FROM user_source
                  )
                  UNION ALL
                  (
                  SELECT name, type, line, UPPER (text)
                  FROM user_source
                  WHERE (name, type) IN
                  (
                  SELECT object_name, object_type
                  FROM user_objects@rem_schema
                  )
                  MINUS
                  SELECT name, type, line, UPPER (text)
                  FROM user_source@rem_schema
                  )
                  )
                  GROUP BY name, type
                  ORDER BY name, type;

                  REM Trigger differences
                  REM ===================

                  PROMPT TRIGGER DISCREPENCIES

                  SET FEEDBACK OFF

                  TRUNCATE TABLE temp_schema_compare;

                  DECLARE
                  CURSOR c1 IS
                  SELECT trigger_name, trigger_body
                  FROM user_triggers;
                  CURSOR c2 IS
                  SELECT trigger_name, trigger_body
                  FROM user_triggers@rem_schema;
                  v_trigger_name VARCHAR2(30);
                  v_trigger_body VARCHAR2(32767);
                  v_hash_value NUMBER;
                  BEGIN
                  OPEN c1;
                  LOOP
                  FETCH c1 INTO v_trigger_name, v_trigger_body;
                  EXIT WHEN c1%NOTFOUND;
                  v_trigger_body := REPLACE (v_trigger_body, ' ', NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
                  v_trigger_body := UPPER (v_trigger_body);
                  v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
                  INSERT INTO temp_schema_compare (database, object_name, hash_value)
                  VALUES (1, v_trigger_name, v_hash_value);
                  END LOOP;
                  CLOSE c1;
                  OPEN c2;
                  LOOP
                  FETCH c2 INTO v_trigger_name, v_trigger_body;
                  EXIT WHEN c2%NOTFOUND;
                  v_trigger_body := REPLACE (v_trigger_body, ' ', NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
                  v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
                  v_trigger_body := UPPER (v_trigger_body);
                  v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
                  INSERT INTO temp_schema_compare (database, object_name, hash_value)
                  VALUES (2, v_trigger_name, v_hash_value);
                  END LOOP;
                  CLOSE c2;
                  END;
                  /

                  SET FEEDBACK 1

                  (
                  SELECT A.trigger_name, 'Local' schema, A.trigger_type,
                  A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
                  referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
                  A.status, B.hash_value
                  FROM user_triggers A, temp_schema_compare B
                  WHERE B.object_name (+) = A.trigger_name
                  AND B.database (+) = 1
                  AND A.table_name IN
                  (
                  SELECT table_name
                  FROM user_tables@rem_schema
                  )
                  MINUS
                  SELECT A.trigger_name, 'Local' schema, A.trigger_type,
                  A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
                  referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
                  A.status, B.hash_value
                  FROM user_triggers@rem_schema A, temp_schema_compare B
                  WHERE B.object_name (+) = A.trigger_name
                  AND B.database (+) = 2
                  )
                  UNION ALL
                  (
                  SELECT A.trigger_name, 'Remote' schema, A.trigger_type,
                  A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
                  referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
                  A.status, B.hash_value
                  FROM user_triggers@rem_schema A, temp_schema_compare B
                  WHERE B.object_name (+) = A.trigger_name
                  AND B.database (+) = 2
                  AND A.table_name IN
                  (
                  SELECT table_name
                  FROM user_tables
                  )
                  MINUS
                  SELECT A.trigger_name, 'Remote' schema, A.trigger_type,
                  A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
                  referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
                  A.status, B.hash_value
                  FROM user_triggers A, temp_schema_compare B
                  WHERE B.object_name (+) = A.trigger_name
                  AND B.database (+) = 1
                  )
                  ORDER BY 1, 2, 5, 3;

                  REM View differences
                  REM ================

                  PROMPT VIEW DISCREPENCIES

                  SET FEEDBACK OFF

                  TRUNCATE TABLE temp_schema_compare;

                  DECLARE
                  CURSOR c1 IS
                  SELECT view_name, text
                  FROM user_views;
                  CURSOR c2 IS
                  SELECT view_name, text
                  FROM user_views@rem_schema;
                  v_view_name VARCHAR2(30);
                  v_text VARCHAR2(32767);
                  v_hash_value NUMBER;
                  BEGIN
                  OPEN c1;
                  LOOP
                  FETCH c1 INTO v_view_name, v_text;
                  EXIT WHEN c1%NOTFOUND;
                  v_text := REPLACE (v_text, ' ', NULL);
                  v_text := REPLACE (v_text, CHR(9), NULL);
                  v_text := REPLACE (v_text, CHR(10), NULL);
                  v_text := REPLACE (v_text, CHR(13), NULL);
                  v_text := UPPER (v_text);
                  v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
                  INSERT INTO temp_schema_compare (database, object_name, hash_value)
                  VALUES (1, v_view_name, v_hash_value);
                  END LOOP;
                  CLOSE c1;
                  OPEN c2;
                  LOOP
                  FETCH c2 INTO v_view_name, v_text;
                  EXIT WHEN c2%NOTFOUND;
                  v_text := REPLACE (v_text, ' ', NULL);
                  v_text := REPLACE (v_text, CHR(9), NULL);
                  v_text := REPLACE (v_text, CHR(10), NULL);
                  v_text := REPLACE (v_text, CHR(13), NULL);
                  v_text := UPPER (v_text);
                  v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
                  INSERT INTO temp_schema_compare (database, object_name, hash_value)
                  VALUES (2, v_view_name, v_hash_value);
                  END LOOP;
                  CLOSE c2;
                  END;
                  /

                  SET FEEDBACK 1

                  (
                  SELECT A.view_name, 'Local' schema, B.hash_value
                  FROM user_views A, temp_schema_compare B
                  WHERE B.object_name (+) = A.view_name
                  AND B.database (+) = 1
                  AND A.view_name IN
                  (
                  SELECT view_name
                  FROM user_views@rem_schema
                  )
                  MINUS
                  SELECT A.view_name, 'Local' schema, B.hash_value
                  FROM user_views@rem_schema A, temp_schema_compare B
                  WHERE B.object_name (+) = A.view_name
                  AND B.database (+) = 2
                  )
                  UNION ALL
                  (
                  SELECT A.view_name, 'Remote' schema, B.hash_value
                  FROM user_views@rem_schema A, temp_schema_compare B
                  WHERE B.object_name (+) = A.view_name
                  AND B.database (+) = 2
                  AND A.view_name IN
                  (
                  SELECT view_name
                  FROM user_views
                  )
                  MINUS
                  SELECT A.view_name, 'Remote' schema, B.hash_value
                  FROM user_views A, temp_schema_compare B
                  WHERE B.object_name (+) = A.view_name
                  AND B.database (+) = 1
                  )
                  ORDER BY 1, 2;

                  REM Job queue differences
                  REM =====================

                  COL what FORMAT a30
                  COL interval FORMAT a30

                  PROMPT JOB QUEUE DISCREPENCIES

                  (
                  SELECT what, interval, 'Remote' schema
                  FROM user_jobs@rem_schema
                  MINUS
                  SELECT what, interval, 'Remote' schema
                  FROM user_jobs
                  )
                  UNION ALL
                  (
                  SELECT what, interval, 'Local' schema
                  FROM user_jobs
                  MINUS
                  SELECT what, interval, 'Local' schema
                  FROM user_jobs@rem_schema
                  )
                  ORDER BY 1, 2, 3;

                  REM Privilege differences
                  REM =====================

                  PROMPT OBJECT-LEVEL GRANT DISCREPENCIES

                  (
                  SELECT owner, table_name, 'Remote' schema, grantee, privilege, grantable
                  FROM user_tab_privs@rem_schema
                  WHERE (owner, table_name) IN
                  (
                  SELECT owner, object_name
                  FROM all_objects
                  )
                  MINUS
                  SELECT owner, table_name, 'Remote' schema, grantee, privilege, grantable
                  FROM user_tab_privs
                  )
                  UNION ALL
                  (
                  SELECT owner, table_name, 'Local' schema, grantee, privilege, grantable
                  FROM user_tab_privs
                  WHERE (owner, table_name) IN
                  (
                  SELECT owner, object_name
                  FROM all_objects@rem_schema
                  )
                  MINUS
                  SELECT owner, table_name, 'Local' schema, grantee, privilege, grantable
                  FROM user_tab_privs@rem_schema
                  )
                  ORDER BY 1, 2, 3;

                  PROMPT SYSTEM PRIVILEGE DISCREPENCIES

                  (
                  SELECT privilege, 'Remote' schema, admin_option
                  FROM user_sys_privs@rem_schema
                  MINUS
                  SELECT privilege, 'Remote' schema, admin_option
                  FROM user_sys_privs
                  )
                  UNION ALL
                  (
                  SELECT privilege, 'Local' schema, admin_option
                  FROM user_sys_privs
                  MINUS
                  SELECT privilege, 'Local' schema, admin_option
                  FROM user_sys_privs@rem_schema
                  )
                  ORDER BY 1, 2;

                  PROMPT ROLE PRIVILEGE DISCREPENCIES

                  (
                  SELECT granted_role, 'Remote' schema, admin_option, default_role, os_granted
                  FROM user_role_privs@rem_schema
                  MINUS
                  SELECT granted_role, 'Remote' schema, admin_option, default_role, os_granted
                  FROM user_role_privs
                  )
                  UNION ALL
                  (
                  SELECT granted_role, 'Local' schema, admin_option, default_role, os_granted
                  FROM user_role_privs
                  MINUS
                  SELECT granted_role, 'Local' schema, admin_option, default_role, os_granted
                  FROM user_role_privs@rem_schema
                  )
                  ORDER BY 1, 2;

                  SPOOL OFF

                  SET TERMOUT ON

                  PROMPT
                  PROMPT Report output written to &report

                  SET FEEDBACK OFF

                  DROP TABLE temp_schema_compare;
                  DROP DATABASE LINK rem_schema;

                  SET FEEDBACK 6
                  SET PAGESIZE 20
                  SET LINESIZE 80
                  • 6. Re: How to compare two oracle database schemas
                    611617
                    You can try Comparenicus from Orbium Software.

                    Compares data and schema (not packages, though).
                    • 7. Re: How to compare two oracle database schemas
                      819264
                      I think it'd be nice to attribute that script. It appears to come from here:
                      http://www.dbspecialists.com/files/scripts/compare_schemas.sql
                      • 8. Re: How to compare two oracle database schemas
                        manei

                        Hi,

                        look this article:

                         

                        SQL Developer: Make the Easy Move

                         

                        saludos

                        Mariano

                        • 9. Re: How to compare two oracle database schemas
                          sb92075

                          Yet another newbie resurrecting a 7+ year old Zombie thread which needs to be LOCKED now.