substr(email, 1, instr(email, '@')-1)

create table t (
  email varchar2(20)
);

insert into t values ('xyz@gmail.com');
insert into t values ('xyz@gmail.co.in');
insert into t values ('xyzef@gmail.com');


select * from (
  select email, 
         substr(email, 1, instr(email, '@')-1) local_part, 
         count(*) over (
           partition by substr(email, 1, instr(email, '@')-1)
         ) ct
  from   t
)
where  ct > 1;

EMAIL                LOCAL_PART         CT
-------------------- ---------- ----------
xyz@gmail.com        xyz                 2
xyz@gmail.co.in      xyz                 2