Create Public Synonims (PL-SQL)

create-public-synonyms.sql

set serveroutput on
DECLARE
    sql_txt VARCHAR2(300);
    CURSOR obj_cur IS
        SELECT object_name, owner FROM ALL_OBJECTS where owner IN ('<owner1>', '<owner2>') and object_type in ('TABLE', 'SEQUENCE', 'VIEW');
BEGIN
    dbms_output.enable(10000000);
    FOR obj IN obj_cur LOOP
        sql_txt:='CREATE OR REPLACE PUBLIC SYNONYM ' || obj.object_name || ' FOR ' || obj.owner || '.' || obj.object_name || ';';
        dbms_output.put_line(sql_txt);
        --execute immediate sql_txt; --uncomment if you want to execute immediately
    END LOOP;
END;