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

SQLselect * from newtab;

SQLcreate synonym s for newtab;

SQLselect * from s;

SQLinsert into s values(20,’a’,’hyd’);

SQLinsert into s(sno,name) values(50,’x’);

SQLselect * from s;

SQLcreate or replace synonym s1 for s;

SQLselect * from s1;

SQLdrop synonym s;

SQLselect* from s1;

SQLcreate synonym s for newtab;

SQLselect * from s1;

SQLdrop table newtab;

SQLselect * from s1;

SQLcreate public synonym emp for emp;

SQLselect * from DICT where like ‘%synonym%’;
  
SQLdesc user_synonym;

SQLdesc all_synonym;

SQLselect table_name from user_synonyms where synonym_name= ‘s’;

SQLselect 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:

SQLselect * from dx;                      //there is no table

SQLcreate synonym snd for dx;      //synonym created

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

SQLselect * from sno;  //synonym transaction is no longer vlid

SQLcreate table dx(sno number(5));

SQLselect * from sno;

SQLinsert into dx values(10);

SQLselect * from sno;

No comments:

Post a Comment