· SQL –
- Synonym is an alternative name that can be
created for following objects: table, view, sequence, stored procedure, function, package, materialized view, java class schema object, user-defined
object or another synonym.
- Synonym is usually used for accessing
objects in another schema to avoid using their full names.
- To be able to create or drop Synonym you
need to be given following grants:
GRANT CREATE SYNONYM TO myUser2;
GRANT CREATE PUBLIC SYNONYM TO myUser2;
GRANT DROP SYNONYM TO myUser2;
GRANT DROP PUBLIC SYNONYM TO myUser2;
Syntax – Create:
- Public Synonyms are accessable to ALL
users, otherwise only user who executed CREATE statement can use it.
CREATE SYNONYM PPL FOR myUser.PEOPLE;
CREATE PUBLIC SYNONYM PPL FOR myUser.PEOPLE;
CREATE OR REPLACE SYNONYM PPL FOR myUser.PEOPLE;
CREATE OR REPLACE PUBLIC SYNONYM PPL FOR myUser.PEOPLE;
Syntax – Delete:
- Synonym can be deleted usin DROP
PUBLIC SYNONYM PPL;
Example – Accessing objects from
- To access table PEOPLE, which is in schema
myUser, myUser2 would need to specify it's full name myUser.PEOPLE.
- To avoid typing myUser.PEOPLE, myUser2 can
create Synonym PPL.
SELECT * FROM myUser.PEOPLE; -- myUser2 is accessing
table PEOPLE in schema myUser.
CREATE SYNONYM PPL FOR myUser.PEOPLE; -- myUser2 created Synonym PPL to avoid using myUser.PEOPLE.
SELECT * FROM PPL; -- myUser2 uses Synonym
PPL to access table myUser.PEOPLE.