Oracle: Synonyms' Synonyms

August 23, 2007

by JP Vijaykumar

Understanding the characteristics of Oracle’s Synonyms and their object access priorities will help resolve related issues.

A synonym is an alias for a schema object.

Oracle restricts the length of object names (tables, columns, views, procedures) to 30 characters, but does not impose the 30 characters length restriction on synonym names. Therefore, you can create synonym names of any length. For testing, I created a synonym of 922 characters long. What if you create a synonym of more than 30 characters long? Let us see:

create or replace public synonym aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
	for temp_jp;
 select length('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') length from dual;
    LENGTH
----------
        30
select * from all_synonyms where table_owner='SCOTT' 
	and table_name='TEMP_JP';
OWNER    SYNONYM_NAME                       TABLE_OWNE          TABLE_NAME 
------   ------------------------------     ----------          ----------
PUBLIC   AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          SCOTT          TEMP_JP

I created a public synonym of 30 characters for my table TEMP_JP. The original name of the synonym is stored in the all_synonyms view.

create or replace public synonym aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
	for temp_jp;
select length('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') length from dual;
    LENGTH
    ----------
        31 

I created a synonym of 31 characters long. Oracle does not impose the length restriction on synonym names.

select * from all_synonyms where table_owner='SCOTT' 
	and table_name='TEMP_JP';
OWNER    SYNONYM_NAME                   TABLE_OWNE  TABLE_NAME 
-------- ------------------------------ ----------  ----------               
PUBLIC   /852936c6_AAAAAAAAAAAAAAAAAAAA SCOTT       TEMP_JP
 select length('/852936c6_AAAAAAAAAAAAAAAAAAAA') length from dual;
LENGTH
----------
        30

Oracle created a 30 character log synonym(short name) for my 31 character long synonym_name.

select * from aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
select * from aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
              *
ERROR at line 1:
ORA-00972: identifier is too long
desc aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
ERROR:
ORA-00972: identifier is too long
ORA-00972: identifier is too long

Even though I could create a synonym name with 31 characters, I can not use the lengthy synonym name to desc or select from the base table.

 desc "/852936c6_AAAAAAAAAAAAAAAAAAAA"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(20)
 select * from "/852936c6_AAAAAAAAAAAAAAAAAAAA";
      COL1 COL2
---------- --------------------
         1 rama

But I can describe or select from the base table, using the Oracle defined synonym for my 31 character long synonym_name.

drop public synonym aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

I could drop the public synonym, using my synonym_name of 31 characters.

In development, you will be creating private synonyms and public synonyms, left and right. If care is not taken, you land in trouble with access priorities. Let us explore the access priorities Oracle chooses, while processing queries on the base tables via synonyms:

 create table temp_jp(col1 number,col2 varchar2(20));
 insert into temp_jp values(1,'rama');
create table temp_jp1(col1 number, col2 varchar2(20));
 insert into temp_jp1 values(1,'sita');
commit;
create user jp identified by jp account unlock;
grant create session to jp;
grant select on temp_jp to jp;
grant select on temp_jp1 to jp;
grant create or replace synonym to jp;
create or replace synonym jp.temp_jp for temp_jp1;
create or replace public synonym temp_jp for temp_jp;

From the above executed sql statements, it is evident that, user JP can select from SCOTT.TEMP_JP table via the public synonym TEMP_JP and from SCOTT.TEMP_JP1 table via the private synonym JP.TEMP_JP in user JP’s schema.

The same is verified by querying the views all_synonyms, all_tab_privs.

select * from all_synonyms where table_name like 'TEMP_JP%';
OWNER           SYNONYM_NAME                     TABLE_OWNE   TABLE_NAME    DB_LINK
----------      ------------------------------   ----------   ----------    ----------
JP              TEMP_JP                          SCOTT        TEMP_JP1
PUBLIC          TEMP_JP                          SCOTT        TEMP_JP
select grantor, grantee, table_schema,table_name,privilege from all_tab_privs
where table_name like 'TEMP_JP%;
GRANTOR  GRANTEE TABLE_SCHEMA    TABLE_NAME PRIVILEGE
-------- ------- --------------- ---------- ----------
SCOTT    JP      SCOTT           TEMP_JP1   SELECT
SCOTT    JP      SCOTT           TEMP_JP    SELECT

Notice, the private synonym in JP’s schema and the public synonym are created with the same nameTEMP_JP.

If user JP executes a query to select from TEMP_JP, how does Oracle decide which base table to access?

Let us see:

connect jp/jp@prod
select * from temp_jp;
      COL1 COL2
---------- --------------------
         1 sita

The private synonym is taking precedence over the public synonym and user JP will be accessing SCOTT.TEMP_JP1 table via JP’s private synonym TEMP_JP.

If we drop the private synonym, from JP’s schema, then user JP can access the data fom scott.TEMP_JP table via the public synonym.

In development, tables are created and dropped regularly. If you do not drop synonyms created on these base tables, the orphaned synonyms will pose a problem down the road.

The synonym remains even after the base table is dropped. If you select from a synonym, on a non existent table, you get an error. You can even create a synonym on a non existent table.

create or replace synonym temp_jp for temp_jp;
create or replace synonym temp_jp for temp_jp
*
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object

create or replace synonym temp_jp for scott.temp_jp;
create or replace synonym temp_jp for scott.temp_jp
*
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object

I can not create a private synonym on an object with an identical name. But I can create a public synonym on an object with an identical name.

create or replace synonym temp_jp for mynonexistenttablename;
select * from temp_jp;
select * from temp_jp
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

create or replace public synonym temp_jp for temp_jp;
select * from temp_jp;
select * from temp_jp
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

create user jp identified by jp account unlock;
grant create session to jp;
grant create or replace synonym to jp;
create or replace synonym jp.temp_jp for temp_jp;
select * from jp.temp_jp;
Select * from jp.temp_jp
                 *
ERROR at line 1:

ORA-01775: looping chain of synonyms

You can create a public synonym on a non existent table. A synonym exists, even when the base table is dropped. But when you access the public synonym on the non existent table, you get an error.

Observe the different error messages generated by Oracle, when selecting from a synonym on a non-existent table with an identical name and with a different name.

References:

Metalink Note:392705.1








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers