Generate DDL for synonyms

https://www.toadworld.com/platforms/oracle/w/wiki/4952.script-to-generate-ddl-for-synonyms


REM ******************************************************************

REM

REM FUNCTION:   Generate DDL for synonyms.
REM
REM ******************************************************************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_SYNONYM_NAME

SET long 1000
SET serveroutput on
SET verify off lines 132

DECLARE
   v_output         CLOB          := NULL;
   v_owner          VARCHAR2 (30) := '&&ENTER_OWNER_NAME';
   v_synonym_name   VARCHAR2 (30) := '&&ENTER_SYNONYM_NAME';
BEGIN
   DBMS_OUTPUT.put_line ('DDL For Database Synonyms');

   FOR tt IN (SELECT owner, synonym_name
                FROM dba_synonyms
               WHERE owner LIKE v_owner AND synonym_name LIKE v_synonym_name)
   LOOP
      SELECT DBMS_METADATA.get_ddl ('SYNONYM', tt.synonym_name, tt.owner)
        INTO v_output
        FROM DUAL;

      DBMS_OUTPUT.put_line (v_output);
   END LOOP;
END;
/