Sunday, May 29, 2011

Synonyms

Tested on an Oracle 11 database.

You can use a synonym to create an alias for an object e.g. a table or view:
 
SQL> create synonym a for b
  2  /
 
Synonym created.
 
SQL> create public synonym x for y
  2  /
 
Synonym created.

SQL>
  
If the object does not exist, you do not get an error until you try to access it:

SQL> desc a
ERROR:
ORA-04043: object "SYSTEM"."B" does not exist
 
SQL> desc x
ERROR:
ORA-04043: object "SYSTEM"."Y" does not exist
 
SQL>

And you can even create a synonym for an object which belongs to a user who does not exist:

SQL> create synonym c for d.e;
 
Synonym created.
 
SQL> select count(*) from dba_users
  2  where username = 'D';
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SQL> desc c
ERROR:
ORA-04043: object "D"."E" does not exist
 
SQL>

No comments: