Extracting Synonyms

Overview

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.