Inserting to Oracle Synonym

Question

If I have a Synonym of a table of another Schema, can I make an insert into the Synonym? If so, will it also affect the original table?

Answer 1

A synonym is not a "real" object, but simply a pointer to an object; so, whatever you do on a synonym, you are doing on the referred object.

You can access a table in a different schema by explicitly writing the schema:

SQL> select count(1) from sys.sys_table;

  COUNT(1)
----------
         0

SQL> select count(1) from sys_table;
select count(1) from sys_table
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Create a synonym

SQL> create synonym my_synonym for sys.sys_table;

Synonym created.

Then you can use the synonym to access to the table:

SQL> select count(1) from my_synonym;

  COUNT(1)
----------
         0

But you still can't directly access the table:

SQL> select count(1) from sys_table;
select count(1) from sys_table
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

You can insert into your table, using the synonym

SQL> insert into my_synonym values (1);

1 row created.

... or using schema.table:

SQL> insert into sys.sys_table values (2);

1 row created.

No matter how you do, all the rows are inserted:

SQL> select count(1) from sys.sys_table;

  COUNT(1)
----------
         2

SQL> select count(1) from my_synonym;

  COUNT(1)
----------
         2

If you drop the synonym

SQL> drop synonym my_synonym;

Synonym dropped.

You need to use schema.table, and the data and the table still remain:

SQL> select count(1) from sys.sys_table;

  COUNT(1)
----------
         2

but, of course, you can no longer use the synonym

SQL> select count(1) from my_synonym;

    select count(1) from my_synonym
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist