| Synonyms [message #28380] |
Wed, 17 December 2003 21:42  |
Shvk
Messages: 26
Registered: December 2003
|
Junior Member |
|
|
Hi All,
Oracle allows creation of public sysnonym on a table that is not created yet. Why this is allowed to happen?
Thanks in advance.
SHVK
|
|
|
|
| Re: Synonyms [message #28391 is a reply to message #28380] |
Thu, 18 December 2003 02:09  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
Good question - I guess it is just one of those things one must get use to.
Note that one can also create views for which tables do not exist (using the FORCE option).
Best regards.
Frank
|
|
|
|
| Re: Synonyms [message #28396 is a reply to message #28380] |
Thu, 18 December 2003 05:43 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Oracle allows creation of a public(or private) synonyms for an object that is not created yet by negative dependency tracking.
For eg)
SQL> drop table test123;
drop table test123
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop synonym test12;
drop synonym test12
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
-- Private synonym created for non-existent test123
SQL> create synonym test12 for test123;
Synonym created.
-- But what actually happens is, Oracle creates an entry in the sys.obj$ table for this non-existent table TEST123 . See below
SQL> select NAME,TYPE# from obj$ where name='TEST123';
NAME TYPE#
------------------------------ ----------
TEST123 10
Type# = 10 indicates that it is 'non-existent'. Once this table gets created, obj$ gets updated promptly. I believe this behaviour is to optimize data dictionary access/updates.
-Thiru
|
|
|
|
| Re: Synonyms [message #28419 is a reply to message #28396] |
Thu, 18 December 2003 22:25 |
Shvk
Messages: 26
Registered: December 2003
|
Junior Member |
|
|
Hi Thiru,
I'm still not clear why Oracle allows this activity becoz this synonym has no relevence if tried to use in code. It will throw the error-- table or view doesn't exist. Pls suggest me out.
Thanks in advance
Shiva
|
|
|
|
| Re: Synonyms [message #28436 is a reply to message #28419] |
Fri, 19 December 2003 04:35 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
Right,thats the expected behaviour(ie error on trying to access a synonym that is pointing to a non-existent object). Oracle maintains an entry in sys.obj$ for the non-existent object for dependency management reasons..
For eg)
SQL> connect thiru/*****
Connected.
SQL> drop public synonym t;
drop public synonym t
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> drop table t;
Table dropped.
-- Create a public synonym for a non-existent table T
SQL> create public synonym t for t;
Synonym created.
-- Create a view based on this Public synonym(actually it could be a table or another view or a private synonym.. Oracle needs this 'non-existent' object to resolve such dependencies and precedence. For eg a local table takes precedence over a public synonym.
SQL> create force view t_view as select * from t;
Warning: View created with compilation errors.
SQL> select name,type# from sys.obj$ where name='T';
NAME TYPE#
------------------------------ ----------
T 2
T 10
T 5
One is the Non-existent table T(type 10), one is the public synonym (type 5) and the other is another table owned by some other user(type 2)
-- Now I am creating a local table
SQL> create table t(x int);
Table created.
-- obj$ is updated from type 10 to type 2
SQL> select name,type# from sys.obj$ where name='T';
NAME TYPE#
------------------------------ ----------
T 2
T 2
T 5
So what happens is that 'local table' T takes precedence over the public synonym with the same name. Its may not be relevant in your code directly,but this behaviour is made use of by Oracle.
If you have a private synonym T ,then you cannot create a table named T in the same schema ,but Creating public synonyms is possible becos of this non-existent object being created and there are performance issues with using tons of public synonyms becos of these non-existent objects in data dictionary.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
