Select unique value pairs that occur (count) more than once inside a table
What you have almost works, just remove the distinct and change the > 2 to > 1.  The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.

drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
insert into tab1 values (2,1);
insert into tab1 values (3,4);
insert into tab1 values (4,3);
insert into tab1 values (5,5);
insert into tab1 values (6,7);
insert into tab1 values (6,7);
insert into tab1 values (6,8);
commit;


SELECT column_fk_id1, column_fk_id2, COUNT(*) AS unique_pair_repeats
FROM tab1
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING count(*) &gt; 1;

COLUMN_FK_ID1 COLUMN_FK_ID2 UNIQUE_PAIR_REPEATS
------------- ------------- -------------------
            1             2                   2
            6             7                   2

2 rows selected.