Search notes:

Oracle: Synonyms

A synonym is an alternate name for one of the following objects:
The following example creates a table named abcd and a synonym for this table, named defg, and then uses both the real table name and the synonym name to insert and select data from the table: 
create table   abcd (a number);
create synonym defg for abcd;

--
-- Insert into abcd:
--
insert into defg values (42);

--
-- Insert another record into abcd, using the synonym:
--
insert into defg values (99);

--
-- Select from abcd, directly and via synonym:
--
select * from abcd;
select * from defg;

--
-- Drop both, the synonym and the table:
--
drop synonym defg;
drop table abcd;

Public and private synonyms

There are two types of synonyms: public and private synonyms.
A public synonym will be used for users.
The owner of a public synonym is the special user PUBLIC.
A private synonym is visible in the schema to which it belongs, or can be accessed by qualifying the synonym name with the schema name of the owner.
The owner of a private synonym is the creator of the synonym.

Example

The following example creates two users to demonstrate public and private synonyms.

Creating test users

A user with enough privileges to create users creates the two demonstration users and grants the necessary privileges to the users: 
create user usr_syn_maker
identified by pw
default tablespace data
quota unlimited on data
temporary tablespace temp;

create user usr_syn_consumer
identified by pw
temporary tablespace temp;

grant
   create session,
   create synonym, create public synonym,
   create table,
   drop   public synonym
to
   usr_syn_maker;

grant
   create session
to
   usr_syn_consumer;

Objects of USR_SYN_MAKER

usr_syn_maker creates two tables and a public and private synonym pointing to these tables: 
connect usr_syn_maker/pw

create table tab_pub (txt varchar2(50));
create table tab_prv (txt varchar2(50));

grant select on tab_pub to usr_syn_consumer;
grant select on tab_prv to usr_syn_consumer;

create public synonym syn_pub for tab_pub;
create        synonym syn_prv for tab_prv;

insert into tab_pub values('A public  synonym points here');
insert into tab_prv values('A private synonym points here');

commit;

USR_SYN_CONSUMER

usr_syn_consumer uses the synonyms to select from the tables: 
connect usr_syn_consumer/pw

select * from syn_pub;

select * from syn_prv;
-- ORA-00942: table or view does not exist

select * from usr_syn_maker.syn_prv;

Using the synonyms in PL/SQL

In addition to using direct SQL, usr_syn_consumer now also creates a PL/SQL procedure to select via the synonyms.
First, the privileged user needs to grant create procedure to usr_syn_consumer`: 
connect / as sysdba

grant create procedure to usr_syn_consumer;
Now, usr_syn_consumer is ready to create the procedure: 
connect usr_syn_consumer/pw

create procedure use_syn authid definer as begin
   for pub in (select txt from syn_pub) loop
       dbms_output.put_line(pub.txt);
   end loop;
   for prv in (select txt from usr_syn_maker.syn_prv) loop
       dbms_output.put_line(prv.txt);
   end loop;
end use_syn;
/

begin
    use_syn;
end;
/

Well known examples for predefined public synonyms

Some well known examples for predefined synonyms include:

Public synonym with the same name as a schema

If a public synonym has the same name as a schema, then all PL/SQL units in that schema will be invalidated.

Privileges

A public synonym can be dropped by anyone who has the dop public synonym system privilege, regardless of who created the synonym.
There is a drop public synonym, but no drop synonym system privilege.

Foreign key constraints

A synonym name can be used when creating a foreign key constraint: 
create table   tq84_p ( id integer primary key );
create synonym tq84_s for tq84_p;
create table   tq84_c ( x references tq84_s );

select
   pk.table_name
from
   user_constraints pk       join
   user_constraints fk on fk.r_constraint_name = pk.constraint_name
where
   fk.table_name = 'TQ84_C';

drop   synonym tq84_s;

drop table tq84_c;
drop table tq84_p;

See also

dba_synonyms
dba_catalog
A synonym can be renamed with the SQL verb rename.
An SQL statement that references synonyms can be transformed into a statement with the identical meaning, but without referencing any synonym (or views for that matter) with dbms_utility.expand_sql_text.
Database objects

Index