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.
In this example, we have 3 basic types of synonym:
- Public synonym
- Private synonym
- Private synonym for a remote table