|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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:
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
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
Existing synonyms can be found via all_synonyms (or dba_synonyms, or user_synonyms).
Thanks to Muhammad Imran who notified me of an error on this page.