Skip to content

How to Get DDL of Synonym from ALL_SYNONYMS

  • by

You don't have to export and import public synonyms by data pump, you can compose the creation statements by querying ALL_SYNONYMS::

[oracle@primary01 oracle]$ vi compose_create_or_replace_synonym.sql

set pagesize 0 linesize 255 echo off hea off feedback off trimspool on timing off;
spool create_or_replace_synonym.sql
select 'set echo on;' from dual;

select 'CREATE OR REPLACE' || case when owner = 'PUBLIC' then ' ' || owner end || ' SYNONYM ' || case when owner <> 'PUBLIC' then owner || '.' end || synonym_name || ' FOR ' || table_owner || '.' || table_name || case when db_link is null then '' else '@' || db_link end || ';' stmt from all_synonyms where table_owner in ('ERPAPP', 'FINAPP') order by 1;

select 'exit;' from dual;
spool off;
exit;

As you can see, the only filter I added in the WHERE clause is TABLE_OWNER in the above statement, you can add some other conditions for your statement.

The execution result of the above SQL statement may look like this.

[oracle@primary01 oracle]$ cat create_or_replace_synonym.sql

set echo on;
CREATE OR REPLACE PUBLIC SYNONYM PAY_DAILY FOR ERPAPP.PAY_DAILY;
CREATE OR REPLACE SYNONYM CRMAPP.CUST_INFO FOR FINAPP.CUST_INFO;
CREATE OR REPLACE SYNONYM CRMAPP.PAY_HIST FOR ERPAPP.PAY_HIST@BOSTON;
...
exit;

Then execute the SQL file in the target database.

Leave a Reply

Your email address will not be published.