This script will produce DDL required to recreate a synonym.
REM LOCATION: Object Management\Synonyms
REM FUNCTION: Generate DDL for synonyms.
REM TESTED ON: 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: dba_synonyms
REM NOTES: Prompts for synonym owner and synonym name. Wildcard accepted.
REM
REM
REM ******************** Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_LINK_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;
/
