René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Synonyms in Oracle

create synonym        synonym-name for object-name;
create public synonym synonym-name for object-name;
A synonym is an alias for one of the following objects:
  • table
  • object table
  • view
  • object view
  • sequence
  • stored procedure
  • stored function
  • package
  • materialized view
  • java class
  • used defined object object type
  • another synonym
The object does not need to exist at the time of its creation.
Synonyms cannot be used in a drop table, drop view or truncate table/cluster statements. If this is tried, it results in a ORA-00942: table or view does not exist

Public synonyms

A public synonym (create public synonym) is owned by public rather than the one who has created it. Therefore, a public synonym is valid for each schema. 
SQL> create table test_ (a number);

Table created.

SQL> create synonym t for test_;

Synonym created.

SQL> insert into t values(4);

1 row created.

SQL> insert into t values(5);

1 row created.

SQL> update t set a =40 where a =4;

1 row updated.

SQL> delete from t where a = 5;

1 row deleted.

SQL> truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist

Listing synonyms

Existing synonyms can be found via all_synonyms (or dba_synonyms, or user_synonyms).

Thanks

Thanks to Muhammad Imran who notified me of an error on this page.