To find out duplicate records from 2 separate schemas

please the follow example:


target_schema1 and target_schema2


table on target_schema2

 create table tamp1
 (text_desc     Varchar2(5),
  Text_id       number);

insert rows :

insert into tamp1 values ('xxxx',1111);
insert into tamp1 values ('zzzz',3333);


table on target_schema1


create table tamp2
 (text_desc     Varchar2(5),
  Text_id       number);

insert rows :

insert into tamp2 values (xxxx,1111);
insert into tamp2 values ('yyyy',2222);

log in to target_schema1 and run :

SQL> grant select on tamp2 to target_schema;

log in to target_schema2 and run :

SQL> grant select on tamp1 to target_schema;


now log in to target_schema1 OR target_schema2 

run the sql query :

SQL> select * from target_schema.tamp1
   2 union all
   3 select * from oe.tamp2;

TEXT_DESC    TEXT_ID
-----      ----------
xxxx           1111
zzzz           3333
xxxx           1111
yyyy           2222



--CREATE A TABLE TO FIND OUT DUPLICATE RECORDS.

SQL> create table test as
  2  select * from target_schema.tamp1
  3  union all
  4* select * from oe.tamp2;

Table created.

SQL> select * from test;

TEXT_    TEXT_ID
----- ----------
xxxx        1111
zzzz        3333
xxxx        1111
yyyy        2222


----and.......there...is...your....duplicate..records!

SQL> select * from test a
  2  where a.rowid>(select min(b.rowid) from test b
  3  where a.text_id = b.text_id);

TEXT_    TEXT_ID
----- ----------
xxxx        1111