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

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;