extract_synonyms is a SQL*Plus script that extracts all public synonyms which reference objects owned by a specific user. It does this by generating a file of CREATE PUBLIC SYNONYM commands that can be used to recreate those synonyms in another database.
Download the Script
Click the following link to view or download the extract_synonyms script: extract_synonyms.sql
Running the extract_synonyms Script
To run this script, you must have access to the DBA_SYNONYMS view. You may need to log in as SYSTEM or SYS in order to see it.
Here is an example showing extract_synonyms being executed to extract all public synonyms that refer to objects owned by the user SYSTEM:
SQL> @c:\jonathan\sql_scripts\extract_synonyms This script allows you to build a SQL*Plus script file which will recreate all PUBLIC synonyms referencing objects in a specified schema.
To abort execution, press ctrl-C. Schema >system Output File >c:\a\system_syn.sql SQL>
The resulting output file will look like this:
create public synonym PRODUCT_PROFILE for SYSTEM.PRODUCT_PRIVS; create public synonym PRODUCT_USER_PROFILE for SYSTEM.PRODUCT_PRIVS; create public synonym TEMP$LOB for SYSTEM.DEF$_TEMP$LOB;
You can easily take this file and execute it on some other database.