Søren's Blog

Random Ramblings

Previous Entry Share Next Entry
A simple way to compare two Oracle schemas

The following SQL can be used to compare two Oracle schemas, for example a staging environment with production.

The first select statement will list all the current users objects (except tables and stuff in the recycle bin). The second select statement will list all the current users columns (and tables), except stuff in the recycle bin.

Send the output to a text file or csv file and compare those.

select owner,object_type,object_name,status
from all_objects 
where owner = (select user from dual)
and object_type != 'TABLE'
and object_name not like 'BIN%'
order by object_type, object_name; 

select c.owner,c.table_name,c.column_name,c.data_type,c.data_length,c.nullable
from all_objects o, all_tab_cols c
where o.owner = (select user from dual)
and o.object_type = 'TABLE'
and o.object_name not like 'BIN%'
and c.owner = o.owner
and c.table_name = o.object_name
order by c.owner,c.table_name,c.column_name;
Tags: ,


Log in

No account? Create an account