You can use a full outer join to achieve this. Here's an example, assuming OBJECT_ID is the common key
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 as select object_id, owner, object_name
2 from dba_objects
3 where rownum <= 10;
Table created.
SQL>
SQL>
SQL> drop table t2 purge;
Table dropped.
SQL> create table t2 as select object_id, owner, case when rownum = 3 then lower(object_name) else object_name end object_name
2 from dba_objects
3 where rownum <= 12;
Table created.
SQL>
SQL>
SQL> select
2 t1.object_id t1_obj,
3 t2.object_id t2_obj,
4 t1.owner t1_own,
5 t2.owner t2_own,
6 t1.object_name t1_name,
7 t2.object_name t2_name
8 from t1
9 full outer join t2
10 on t1.object_id = t2.object_id
11 where
12 t1.object_id is null
13 or t2.object_id is null
14 or decode(t1.owner,t2.owner,1,0) = 0
15 or decode(t1.object_name,t2.object_name,1,0) = 0
16 ;
T1_OBJ T2_OBJ T1_OWN T2_OWN T1_NAME T2_NAME
---------- ---------- -------------------- -------------------- ------------------------------ ------------------------------
32 32 SYS SYS CCOL$ ccol$
6 SYS C_TS#
48 SYS I_COL1
So the three circumstances are catered for:
in T1, not in T2 => t2.obj is null
in T2, not in T1 => t1.obj is null
difference in data => the decode statements
I'm using DECODE because it handles nulls nicely as well, eg, I'll add some data:
SQL> insert into t2 values (-1,'x','y');
1 row created.
SQL> insert into t2 values (-2,'z',null);
1 row created.
SQL> insert into t1 values (-1,'x',null);
1 row created.
SQL> insert into t1 values (-2,'z',null);
1 row created.
So even though there are nulls (and null != null), in this case, we *probably* want to see "-1" because the object_name is differnet, and not see -2, because both are null.
SQL> select
2 t1.object_id t1_obj,
3 t2.object_id t2_obj,
4 t1.owner t1_own,
5 t2.owner t2_own,
6 t1.object_name t1_name,
7 t2.object_name t2_name
8 from t1
9 full outer join t2
10 on t1.object_id = t2.object_id
11 where
12 t1.object_id is null
13 or t2.object_id is null
14 or decode(t1.owner,t2.owner,1,0) = 0
15 or decode(t1.object_name,t2.object_name,1,0) = 0
16 ;
T1_OBJ T2_OBJ T1_OWN T2_OWN T1_NAME T2_NAME
---------- ---------- -------------------- -------------------- ------------------------------ --------
32 32 SYS SYS CCOL$ ccol$
6 SYS C_TS#
48 SYS I_COL1
-1 -1 x x y
4 rows selected.
You can tailor this into an insert statement or whatever you like.