SQL Basics-Synonyms

The concept of synonyms:

Synonyms are aliases of Oracle objects and use synonyms to access the same objects

You can create synonyms for objects such as tables, views, stored procedures, functions, or another synonym

Easy access to other users ' objects, hiding the identity of the object

Shorten the length of the object name

Synonym creation Syntax:

　　CREATE [public] synonym synonym_name for object;

Permissions to create and delete synonyms:

• CREATE any synonym
• CREATE public synonym
• DROP public synonym
• DROP any synonym

Examples of creating synonyms:

Sql>Conn/  asSYSDBA is connected. SQL> Grant Create  PublicSynonym toScott; authorized success. SQL> Grant Create  anySynonym toScott; authorized success. SQL>Conn Scott/Tiger is connected. SQL> Create  PublicSynonym Mydept forDept; # #创建公有同义词, other users can also access synonyms created. SQL> CreateSynonym Mydept forDept; # #创建私有同义词, only Scott can access it, with the same name as a public synonym, and there is no error that the synonym has been created. 

To delete a synonym:

Sql> Drop  Publicsynonym mydept; synonyms have been deleted. SQL> Select *  frommydept;    # #删除共有同义词, the private ones are still there DEPTNO dname LOC---------- -------------- -------------    TenACCOUNTING NEW YORK -DALLAS -SALES CHICAGO +OPERATIONS Bostonsql> Dropsynonym mydept; synonyms have been deleted.
SQL> Select *  frommydept;Select *  frommydept*Section1line error: ORA-00942: Table or view does not exist

To view a synonym view:

Sql> Select *  fromuser_synonyms; Synonym_name Table_owner table_name------------------------------ ------------------------------ ------------------------------Db_link------------------------------------------------------------------------------------------------------------- -----------mydept SCOTT Deptsql>Conn/  asSYSDBA is connected. SQL> SELECTSynonym_name,table_owner,table_name fromDba_synonymsWHEREtable_name= 'DEPT'; Synonym_name Table_owner table_name------------------------------ ------------------------------ ------------------------------mydept Scott Deptmydept Scott DEPT

Note: Public synonyms created do not appear in the user_synonyms view

SQL Basics-Synonyms