Query below compares columns (names) in tables between two Oracle Database schemas. It shows columns missing in either of two databases.

Query

select nvl(s1.table_name, s2.table_name) as table_name,
       nvl(s1.column_name, s2.column_name) as column_name,
       s1.column_name as schema_1,
       s2.column_name as schema_2
from ( select table_name,
              column_name
       from sys.all_tab_cols
       where owner = 'schema_1'       -- put schema name to compare here
) s1
full join ( select table_name,
                   column_name
            from sys.all_tab_cols
            where owner = 'schema_2'  -- put schema name to compare here
) s2 on s2.table_name = s1.table_name
     and s2.column_name = s1.column_name
where s1.column_name is null
      or s2.column_name is null
order by table_name,
         column_name;

Columns

• table_name - name of the table with schema
• column_name - name of column
• schema_1 - if column exists in a table in schema 1 then column contains its name (repeats it from column column)
• schema_2 - if column exists in a table in schema 2 then column contains its name (repeats it from column column)

Rows

• One row represents one distinct name of column in specific table.
• Scope of rows: all distinct columns in that exist only in one of the compared databases.
• Ordered by table and column name

Sample results