Re: Compare table structures between two schema

One way I do it is to create a database link called TEMP_LINK on one database poitning to the other.
Then run a a couple of select stattments similar to the following.  (substutue ID1 and ID2 for the 2 schema owners.)

column table_name format a18
column column_name format a18
column object_name format a18
column data_type format a10
PROMPT TABLE Columns owned by ID1 not by ID2 @ SID2 select table_name, column_name, data_type, data_length, nullable  from dba_tab_columns where
  owner = 'ID1'
  minus
  select table_name, column_name, data_type, data_length, nullable  from dba_tab_columns_at_TEMP_LINK where
  owner = 'ID2' ;

PROMPT TABLE Columns owned by ID2 @ SID2 not by ID1 select table_name, column_name, data_type, data_length, nullable  from dba_tab_columns_at_TEMP_LINK where
  owner = 'ID1'
  minus
  select table_name, column_name, data_type, data_length, nullable  from dba_tab_columns where
  owner = 'ID2';

Best of luck:

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'

"Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message news:7ca2852.0308192217.65d42a2c_at_posting.google.com...
> Hi
> I am maintaining a customized Oracle 11.5.4 setup. I would like to
> know what tables and objects have been modified from the base or
> standard 11i setup. I plan to achieve this by comparing the customized
> setup with another standard 11.5.4 setup by using the dba_tab_columns
> where i will get to know the columns,datatypes,length/precision etc.
> Can somebody guide me as to how to go about it ? Do i take the output
> into a flat file and then import into excel to compare or can i write
> a shell script that can compare the outputs and give the differing
> tablename/columnname etc. Either case give me some hints.
>
> Thanks,
> Ashok
Received on Wed Aug 20 2003 - 05:48:08 CDT