Hi ,tom
I am currently working with synonym in oracle and find the fact that:
SQL> create public synonym mysynonym for myschema.mytable;
Synonym created.
SQL> create table myschema.mytable(a int);
ERROR at line 1:
ORA-00955: name is already used by an existing object
According to the doc of oracle described here, it is quite reasonable:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm yeah, it is quite reasonable, expected. but I find that 'vice versa' does not hold here:
SQL> create table myschema.mytable(a int);
Table created. //create a table firstly
SQL> create public synonym mysynonym for myschema.mytable;
Synonym created.
// and then create a public synonym with the same name of the table.
// wow, succeed. IMHO, it should have been failed.
would you please help me figure out why ?
thanks a lot.
We have several namespaces inside the database, which defines where names are allowed to overlap, and where they are not.
SQL> select distinct namespace, object_type from dba_objects
2 order by 1,2;
NAMESPACE OBJECT_TYPE
---------- -----------------------
1 CHAIN
1 DESTINATION
1 FUNCTION
1 INDEXTYPE
1 JAVA CLASS
1 JOB
1 JOB CLASS
1 LIBRARY
1 OPERATOR
1 PACKAGE
1 PROCEDURE
1 PROGRAM
1 SCHEDULE
1 SCHEDULER GROUP
1 SEQUENCE
1 SYNONYM
1 TABLE
1 TABLE PARTITION
1 TABLE SUBPARTITION
1 TYPE
1 VIEW
1 WINDOW
2 PACKAGE BODY
2 TYPE BODY
3 TRIGGER
4 INDEX
4 INDEX PARTITION
5 CLUSTER
8 LOB
8 LOB PARTITION
9 DIRECTORY
10 QUEUE
13 JAVA SOURCE
14 JAVA RESOURCE
19 MATERIALIZED VIEW
20 DIMENSION
21 CONTEXT
23 RULE SET
24 CONSUMER GROUP
24 RESOURCE PLAN
25 XML SCHEMA
32 JAVA DATA
36 RULE
38 EVALUATION CONTEXT
51 UNDEFINED
52 UNDEFINED
64 EDITION
88 UNDEFINED
92 SQL TRANSLATION PROFILE
93 UNIFIED AUDIT POLICY
DATABASE LINK
51 rows selected.
So as long as you have uniqueness within the namespace you'll be ok. A public synonym is simply an object owned by the special user "PUBLIC", so
PUBLIC.MY_SYNONYM
PUBLIC.MY_TABLE
MYSCHEMA.MY_TABLE
can all co-exist fine.