Comparison of database schemas

Suppose we need to compare two database schemas to find out changes such as new tables, columns, and change of data type.

Test

create user USER1 identified by USER1;
create user USER2 identified by USER2;
create table USER1.T1 (C int);
create table USER2.T1 (C int);
create table USER1.T2 (C int);
create table USER2.T3 (C int);
create table USER1.T4 (C int, D int);
create table USER2.T4 (C int);
create table USER1.T5 (C int, E int);
create table USER2.T5 (C int, E varchar(20));

Result

Schema comparison

Comparison script

select
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  OWNER,
  case
    when COLUMN_NAME = ' '
    then 'Table'
    else
      case
        when DATA_TYPE = ' '
        then 'Column'
        else 'Type'
      end
  end DIFF_TYPE
from
(
  (
    select
      C.TABLE_NAME,
      C.COLUMN_NAME,
      case
        when D_COLUMNS.TABLE_NAME is null
        then C.DATA_TYPE
        else ' '
      end as DATA_TYPE,
      C.OWNER
    from
      DBA_TAB_COLUMNS C
    inner join
      (
        (
          select TABLE_NAME, COLUMN_NAME, DATA_TYPE
          from DBA_TAB_COLUMNS
          where OWNER = 'USER1'
        minus
          select TABLE_NAME, COLUMN_NAME, DATA_TYPE
          from DBA_TAB_COLUMNS
          where OWNER = 'USER2'
        )
        union all
        (
          select TABLE_NAME, COLUMN_NAME, DATA_TYPE
          from DBA_TAB_COLUMNS
          where OWNER = 'USER2'
        minus
          select TABLE_NAME, COLUMN_NAME, DATA_TYPE
          from DBA_TAB_COLUMNS
          where OWNER = 'USER1'
        )
      ) D_TYPE
    on
      C.TABLE_NAME = D_TYPE.TABLE_NAME
    and
      C.COLUMN_NAME = D_TYPE.COLUMN_NAME
    and
      C.DATA_TYPE = D_TYPE.DATA_TYPE
    left join
      (
        (
          select TABLE_NAME, COLUMN_NAME
          from DBA_TAB_COLUMNS
          where OWNER = 'USER1'
        minus
          select TABLE_NAME, COLUMN_NAME
          from DBA_TAB_COLUMNS
          where OWNER = 'USER2'
        )
        union all
        (
          select TABLE_NAME, COLUMN_NAME
          from DBA_TAB_COLUMNS
          where OWNER = 'USER2'
        minus
          select TABLE_NAME, COLUMN_NAME
          from DBA_TAB_COLUMNS
          where OWNER = 'USER1'
        )
      ) D_COLUMNS
    on
      C.TABLE_NAME = D_COLUMNS.TABLE_NAME
    and
      C.COLUMN_NAME = D_COLUMNS.COLUMN_NAME
    left join
      (
        (
          (
            select TABLE_NAME
            from DBA_TABLES
            where OWNER = 'USER1'
          union 
            select VIEW_NAME
            from DBA_VIEWS
            where OWNER = 'USER1'
          )
        minus
          (
            select TABLE_NAME
            from DBA_TABLES
            where OWNER = 'USER2'
          union 
            select VIEW_NAME
            from DBA_VIEWS
            where OWNER = 'USER2'
          )
        )
        union all
          select VIEW_NAME
          from DBA_VIEWS
          where OWNER = 'USER2'
        union all
          select VIEW_NAME
          from DBA_VIEWS
          where OWNER = 'USER1'
      ) D_TABLES
    on
      C.TABLE_NAME = D_TABLES.TABLE_NAME
    where
      C.OWNER in ('USER1', 'USER2')
    and
      D_TABLES.TABLE_NAME is NULL
  )
  union all
  (
    select
      TABLE_NAME,
      ' ' as COLUMN_NAME,
      ' ' as DATA_TYPE,
      OWNER
    from DBA_TABLES
    where TABLE_NAME in
      (
        (
          select TABLE_NAME
          from DBA_TABLES
          where OWNER = 'USER1'            
        minus
          select TABLE_NAME
          from DBA_TABLES
          where OWNER = 'USER2'
        )
        union all
        (
          select TABLE_NAME
          from DBA_TABLES
          where OWNER = 'USER2'
        minus
          select TABLE_NAME
          from DBA_TABLES
          where OWNER = 'USER1'
        )
      )
    and
      OWNER in ('USER1', 'USER2')
  )
) 
order by
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  OWNER;