PL/SQL Script to delete all public synonyms for a schema

drop-public-synonyms.sql

SET serveroutput ON
BEGIN
   FOR cur_syn IN (SELECT synonym_name
                     FROM all_synonyms
                    WHERE table_owner = upper('schema_user'))
   LOOP
      BEGIN
         EXECUTE IMMEDIATE ('drop public synonym ' || cur_syn.synonym_name ||' ');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE ('Failed to drop the public synonym ' || cur_syn.synonym_name || '!');
      END;
   END LOOP;
END;
/