Delete Duplicate rows in a table in Oracle

 Delete Duplicate rows in a table in Oracle

-- replace table name and column names according to your requirement

DECLARE

CURSOR C1 IS  
select phone_no,service_type,count(1) 

from nz_wkglines_ftth group by 
 phone_no,service_type 

having count(1)>1;
 
BEGIN

    FOR V1 IN C1 LOOP   
     
    DELETE FROM  nz_wkglines_ftth WHERE ROWID NOT IN
    (
    SELECT ROWID FROM nz_wkglines_ftth WHERE 
    phone_no=V1.phone_no  and 
    service_type =v1.service_type
    AND ROWNUM<2
    ) and   phone_no=V1.phone_no  and 
    service_type =v1.service_type
    ;
     
      COMMIT;
    END LOOP;
   
END;