Re: automatic schema comparison

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 03 Dec 2002 17:04:44 GMT
Message-ID: <Mu5H9.111$OD4.5166435_at_newssvr13.news.prodigy.com>

Betty Zhang wrote:
> Hi,
>
> There are hundreds of almost identical schemas in a DB, I want to
> write a script to check the objects(tables, triggers,
> packages...)differences between those schemas and the standard schema,
> and save the differences in a text file.
>
> Currently, I have 2 ideas to do it.
> 1. spool object source codes into text files, then diff them.
> get package,function..etc info by using spool file:
> "select text from user_source where type='package'"....
> but I didn't find table definition, I have to use dba_tables and
> dba_tab_cloumns to compare schema tables.
>
> I am not satisfied with above 1.
>
> 2. making use of Toad. There is a 'schema differences' in 'view'
> menu.
> But I don't know how to call it from command line.
>
> Who has good idea? Could you tell me?
> Thanks for your nice instructions!
>
> Betty Zhang
I wrote a series of sql/plsql scripts to compare various objects. Here's the one for tables (most of it is comments ... making it a long post):

• This script was written, primarily, to assist in comparing database
• objects between two different schema owners. This script deals with: --
• Tables --
• The script allows a Configuration Manager to verify that the
• objects are identical in both schema owner spaces (aka, databases).
• If differences are found, this fact may - or may not - imply that
• changes need to be made to one or the other schema. --
• This script generates a listing in the following general format: --
• Table: <table_name_1>
• ..Column: <column_name_1> <datatype> <nullable>
• ...
• ..Column: <column_name_n> <datatype> <nullable>
• ... --
• Table: <table_name_n>
• ..Column: <column_name_1> <datatype> <nullable>
• ...
• ..Column: <column_name_n> <datatype> <nullable> --
• The information is gleaned from the following data dictionary
• view(s):
• USER_TABLES
• USER_TAB_COLUMNS --
• EXAMPLE RUN:
• ----------- --
• Example Assumptions:
• o First schema owner in comparison = engdesk (grizzly)
• o Second schema owner in comparison = sparky (melon)
• o Use SQL*Plus
• o Use tkdiff as difference comparison application --
• Example Steps:
• o Start SQL*Plus
• o Connect to first schema owner (if not already connected):
• - SQL> connect grizzly/secret_password_at_engdesk
• o Spool output for first schema owner:
• - SQL> spool c:\grizzly_tables.lst
• o Run this script to generate first listing:
• - SQL> _at_list_table_layout
• o Close spooled output:
• - SQL> spool off
• o Connect to second schema owner:
• - SQL> connect melon/secret_password_at_sparky
• o Spool output for first schema owner:
• - SQL> spool c:\melon_tables.lst
• o Run this script to generate second listing:
• - SQL> _at_list_table_layout
• o Close spooled output:
• - SQL> spool off
• o Open an MS-DOS window:
• - SQL> host
• o Compare the two generated files (in DOS window):
• -- C:\oracle\ora81\bin> cd \
• - C:\> tkdiff grizzly_tables.lst melon_tables.lst
• o Clean up and return to SQL*Plus window
• - C:\> del grizzly_tables.lst
• - C:\> del melon_tables.lst
• - C:\> exit

  ---

set echo off feedback off pause off pagesize 0 set serveroutput on size 100000

---

DECLARE
   --
   cursor c_user_tables is

     select table_name
     from   user_tables
     order by table_name;

   --
   cursor c_user_tab_columns ( vin_table_name in varchar2 ) is

     select table_name,
            column_name,
            data_type,
            decode ( data_type,
              'NUMBER',    '(' || data_precision || ',' || data_scale || 
')',
              'VARCHAR2',  '(' || data_length    || ')',
              'NVARCHAR2', '(' || data_length    || ')',
              'CHAR',      '(' || data_length    || ')',
              'NCHAR',     '(' || data_length    || ')',
              ''
            )                      decode_data_type,
            decode ( nullable,
              'N',  ' NOT NULL',
              ''
            )                      decode_nullable
     from   user_tab_columns
     where  table_name = vin_table_name
     order by column_id;

   --
BEGIN
   --
   dbms_output.enable ( 100000 );
   --

• Display header. dbms_output.put_line ( chr(10) || 'Table Layout Listing' ); --
• For each table... for r_user_tables in c_user_tables loop --
  • Display table name. dbms_output.put_line ( chr(10) || 'Table: ' || r_user_tables.table_name ); --
  • For each column... for r_user_tab_columns in c_user_tab_columns ( r_user_tables.table_name ) loop --
    • Display column attributes. dbms_output.put_line ( '..Column: ' || r_user_tab_columns.column_name || ' ' || r_user_tab_columns.data_type || r_user_tab_columns.decode_data_type || r_user_tab_columns.decode_nullable ); -- end loop; -- End for each column... -- end loop; -- End for each table... -- END; /

      ---

set serveroutput off
set feedback on pagesize 24 Received on Tue Dec 03 2002 - 18:04:44 CET