24. SYNONYMS

Synonyms

          They are the alternate names to tables, views, synonyms and so on……

·         They are virtual/mirror objects

·         Useful in frontend application and to hide the table owner name

·         Useful in distribution databases

·         They don’t have their own structure(it won’t occupy any memory area)

·         They are simply logical names

·         They again depends on base tables for SQL statements

·         It is not possible to alter the structure of base table by using synonyms

·         We can define synonym on another synonym

·         A table can have ‘n’ no of synonyms but each synonym can be defined only on single table, which means a synonym can’t hold more than one table

·         We can also define synonym on non_exixting objects

·         It is not possible to hide partial part of table data by using synonym like views

·         They are two types of synonyms

a.    Public synonym

b.    Private synonym

·         Private synonym share the same table name space

·         Public synonym does not share same table name space, so we can define public synonym names with table names

·         If you drop the synonym base table won’t get effect but, if you drop base table , synonym becomes invalid

Syn:  SQL> create [public] synonym for ;

·         Synonym and views are subjected to table constraints

·         We can’t create synonym for constraints

·         Distribution database objects means more than one database

SQL> select * from newtab;

SQL> create synonym s for newtab;

SQL> select * from s;

SQL> insert into s values(20,’a’,’hyd’);

SQL> insert into s(sno,name) values(50,’x’);

SQL> select * from s;

SQL> create or replace synonym s1 for s;

SQL> select * from s1;

SQL> drop synonym s;

SQL> select* from s1;

SQL> create synonym s for newtab;

SQL> select * from s1;

SQL> drop table newtab;

SQL> select * from s1;

SQL> create public synonym emp for emp;

SQL> select * from DICT where like ‘%synonym%’;

  

SQL> desc user_synonym;

SQL> desc all_synonym;

SQL> select table_name from user_synonyms where synonym_name= ‘s’;

SQL> select synonym_name from user_synonym where table_name=’emp’;

User_synonym:

Table name	Synonym name
T S	S S1

Finding out the synonym for other synonym

SQL>select table_name from user_synonym where synonym_name=’1’;

Creating synonym on non-existing object:

SQL> select * from dx;                      //there is no table

SQL> create synonym snd for dx;      //synonym created

Note: Table/view is not existed for dx, but synonym created we can do this.

SQL> select * from sno;  //synonym transaction is no longer vlid

SQL> create table dx(sno number(5));

SQL> select * from sno;

SQL> insert into dx values(10);

SQL> select * from sno;