·  SQL Synonym

 

- Info:

  - 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.

 

- Grants:

  - 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 statement.

    DROP        SYNONYM PPL;

    DROP PUBLIC SYNONYM PPL;

 

- Example – Accessing objects from another schema:

  - 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.