Compare Table structure between two users:Compare table structure between the two users identified by variables: 'user_source' and 'user_dest'.
(Compare column names, data types, length, precision, column order, nullable).
The Script's output is the list of tables that have difference in structure between the two schemas or tables that exist in only one of the schemas.
The Script:set serveroutput on
declare
user_source varchar2(30) default '
SYSTEM'; --Source user
user_dest varchar2(30) default '
SYS'; --Destination user
begin
for crs in (select distinct TABLE_NAME from
((select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
from dba_tab_columns where owner = upper(user_source)
minus
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
from dba_tab_columns where owner = upper(user_dest))
union
(select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
from dba_tab_columns where owner = upper(user_dest)
minus
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
from dba_tab_columns where owner = upper(user_source)))) loop
dbms_output.put_line(crs.table_name);
end loop;
end;
/
