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