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;
No comments:
Post a Comment