Comparing Table Structure in ORACLE

Query to Compare Table Structure(Columns/Data Types) in ORACLE

You can use below SQL Queries to compare two tables based on your requirement:

WITH 
T1 As (Select USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE From USER_TAB_COLUMNS Where Table_Name='EMP_TEST'),
T2 As (Select USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE From USER_TAB_COLUMNS Where Table_Name='DEPT_TEST')
Select 
  T1.COLUMN_NAME EMP, 
  T1.DATA_TYPE EMP_TYPE, 
  T2.COLUMN_NAME DEPT, 
  T2.DATA_TYPE DEPT_TYPE  
From T1 Full Join T2 
On (T1.COLUMN_NAME=T2.COLUMN_NAME);

Output:

You can also use below Query:

Select 'TABLE',Column_Name, Data_Type
From 
(
Select Column_Name, Data_Type 
From USER_TAB_COLUMNS
Where Table_Name='EMP_TEST'
Minus
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='DEPT_TEST') 
Union All
Select 'DEPT',Column_Name, Data_Type 
From 
(
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='DEPT_TEST'
Minus
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='EMP_TEST'
);

Output: