Get names of all synonyms from Oracle database

Get names of all synonyms from Oracle database

To get names of all synonyms from Oracle database or from an specific table you can use: USER_SYNONYMS, ALL_SYNONYMS, DBA_SYNONYMS, USER_OBJECTS.

USER_SYNONYMS: This view contains information about the synonyms that have been created by the current user. It provides information such as the name of the synonym, the object it references, and the schema it belongs to.

ALL_SYNONYMS: This view contains information about all the synonyms that are accessible to the current user. It includes synonyms created by the user, as well as synonyms created by other users that the user has access to.

DBA_SYNONYMS: This view contains information about all the synonyms in the database, including those created by the user, other users, and the database administrator. This view can only be accessed by users with the DBA role.

These views can be useful for managing synonyms and troubleshooting issues related to them in the database.

Privileges may be required to query certain tables or views. The most commonly used to list synonyms in a database are USER_SYNONYMS and USER_OBJECTS.

Examples

  
SELECT * FROM USER_SYNONYMS;

SELECT * FROM USER_SYNONYMS 
WHERE TABLE_OWNER='SYSTEM' 
AND TABLE_NAME='TEST';

SELECT * FROM ALL_SYNONYMS;

SELECT * FROM ALL_SYNONYMS 
WHERE TABLE_OWNER='SYSTEM' 
AND TABLE_NAME='TEST';

SELECT * FROM DBA_SYNONYMS;

SELECT * FROM DBA_SYNONYMS 
WHERE TABLE_OWNER='SYSTEM' 
AND TABLE_NAME='TEST';

SELECT * FROM USER_OBJECTS 
WHERE OBJECT_TYPE='SYNONYM';

SELECT * FROM USER_OBJECTS 
WHERE OBJECT_TYPE='SYNONYM' 
AND ORACLE_MAINTAINED='N';