compare tables metadata of two schemas

1. find different table column definition of schema1 from shcema2
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)

2. find difference table column definition of schema2 from schema1

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);

3. find difference table column definition both of schema1 from shcema2 and of schema2 from schema1

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)
union
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);


4 to run it in a sql script

accept SCHEMA1 prompt ' input schema 1 name: '
accept SCHEMA2 prompt ' input schema 2 name: '

select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
)
union
select owner, table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA2')
and (table_name, column_name, data_type, data_length) not in (
select table_name, column_name, data_type, data_length from dba_tab_columns where owner=upper('&SCHEMA1')
);