Public Synonyms in Oracle PL/SQL

by Andy
Posted on March 12, 2018May 20, 2021

Public Synonyms

By creating a Public Synonym you allow the user to reference the table name without using the schema owner prefixed on the record name (i.e. SYSADM.PS_VENDOR). This also eliminates the need to perform an ALTER SESSION command.

Example:

/* Note: the Public Synonym should be the same name as the record */
CREATE PUBLIC SYNONYM MY_TABLE_NAME FOR MY_TABLE_NAME;

1 2	/* Note: the Public Synonym should be the same name as the record */ CREATE PUBLIC SYNONYM MY_TABLE_NAME FOR MY_TABLE_NAME;

How to find tables that do not have a public synonym.

Use the following example to find tables that do not have a public synonym.

SELECT 'CREATE PUBLIC SYNONYM ' || OBJECT_NAME || ' FOR ' || OBJECT_NAME || ';'
FROM DBA_OBJECTS A
WHERE A.OBJECT_TYPE IN ('TABLE', 'VIEW')
      AND A.OBJECT_NAME LIKE 'PS_MYTABLE%'
      AND A.TIMESTAMP > '01-MAR-2005'
      AND NOT EXISTS (SELECT '1' FROM DBA_SYNONYMS B
                      WHERE B.OWNER = 'PUBLIC' AND
                      B.SYNONYM_NAME = A.OBJECT_NAME)
ORDER BY TIMESTAMP DESC

SELECT 'CREATE PUBLIC SYNONYM ' || OBJECT_NAME || ' FOR ' || OBJECT_NAME || ';'

FROM DBA_OBJECTS A

WHERE A.OBJECT_TYPE IN ('TABLE', 'VIEW')

AND A.OBJECT_NAME LIKE 'PS_MYTABLE%'

AND A.TIMESTAMP > '01-MAR-2005'

AND NOT EXISTS (SELECT '1' FROM DBA_SYNONYMS B

WHERE B.OWNER = 'PUBLIC' AND

B.SYNONYM_NAME = A.OBJECT_NAME)

ORDER BY TIMESTAMP DESC