Comparing structure of two shemas 2

[roedelfroe]

Hi everybody,

following problem:

We have a test and production database (nothing unusual so far ). For some reasons, changes in the test database were never documented and I never got any reports when changes were made.

As I am responsible for the production database I have now the problem, that I simply don't know, what changes were made in test (e.g. deleted or added colums, new tables...).

Any hints how to compare both shemas?

Thanks in advance

Roland

 

[ddrillich]

Some DB tools provide schemas’ comparison. For example, Toad (the non-free version) has this feature.

Regards,
Dan

 

[SantaMufasa]

Roe,

If you don't have TOAD, then here is a script that lists all columns in SchemaA that differ from SchemaB and all columns in SchemaB missing from SchemaA. (You will want to save the following code to a script, then run the script, due to ACCEPT statements.)

col a Heading "Table|Name" format a30
col b Heading "Column|Name" format a30
col c Heading "Data|Type" format a30
col d Heading "Length" format 9999
col e Heading "Num|Digits" format 999
col f Heading "Num|Decimals" format 999
set pagesize 35
clear breaks
break on a
accept ProdSchema prompt "Enter the name of the Production Schema: "
accept TestSchema prompt "Enter the name of the Test Schema: "
ttitle left "Columns in '&TestSchema' schema that differ from '&ProdSchema' schema"
select	  table_name a
	, column_name b
	, data_type c
	, data_length d
	, data_scale e
	, data_precision f
from dba_tab_columns where owner = upper('&TestSchema')
minus
select table_name, column_name, data_type, data_length, data_scale, data_precision
from dba_tab_columns where owner = upper('&ProdSchema')
order by a, b
/
ttitle left "Columns in '&ProdSchema' schema that are missing from '&TestSchema' schema"
select table_name a, column_name b from dba_tab_columns where owner = upper('&ProdSchema')
minus
select table_name a, column_name b from dba_tab_columns where owner = upper('&TestSchema')
order by a, b
/
ttitle off

Let me know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:31 (19Feb04) UTC (aka "GMT" and "Zulu&quot, 09:31 (19Feb04) Mountain Time)

 

[aryeh1010]

Mufasa's script is good when you are talking about two schemas in the same database.

Since you said that you were talking about two different databases, don't forget to use a database link and modify the script accordingly.

In addition to dba_tables, you'll probably want to check dba_indexes for indexes that have been added or removed, dba_ind_columns (for indexes that have been modified), and the same for dba_constraints and dba_cons_columns.

Have fun,

Aryeh Keefe

 

[roedelfroe]

Hi Mustafa,
hi Aryeh

this was exactly what I needed.

Thank you all

Greetings

Roland