wierd problem - process hangs - what is happening ?

This one is pretty strange (on 8.1.5 on Compaq TRU64):

I have a public synonym for a table named: trades
it points to scott.trades

say I am connected as system (or even connect internal)
if I try to desc it (thru the public synonym), it hangs
if I try to drop the public synonym, it hangs
if I try to select it (thru the public synonym), it hangs
if I try to create a table with the same name "trades", it hangs

I cannot drop the table, since it is parititioned, and would be a lot of work to recreated, etc.

if I try to select it (thru schema.table_name), i can actually select it !!! So that proves that the table/partitions are not corrupted.

There are no errors in the alert log, or any other log anywhere !!

What should I look at now ???

try granting inset/update/select/delete to others as the synonym owner and try


Here is the possible cause for the error.
1. You don't have the correct access privileges on the table
2. When you create synonym you create it as

Code:

---

        CREATE PUBLIC SYNONYM synonym_name
        FOR schema.object_name;

---

Sam

[Edited by sambavan on 01-08-2002 at 04:31 PM]

I tried a few things:

if I connect as the table owner, I can desc, select (since the public synonym) is not used. But I still cannot drop the synonym.

as for the issue of privileges, I connected as a DBA account, and tried to grant select to another user, it hangs ?

I did notice that a CORE file was generated from the place that I was running the sqlplus session, I will submit it or our friends as ORACLE.

What was your point about the CODE for creating the synonym ? are you saying what you posted is in-correct ?

Apart from grants the other aspect is that public synonyms cause some issues during the parse phase some additional actions are performed due to negative dependencies checking.

Negative dependencies checking ensures that no object with name involved in SQL statement is stored in parsing user schema. In case of massive use of public synonyms this implies additional load for dictionary cache and library cache.

This may affect the performance due to increased contention of shared pool, library cache and dictionary cache areas. Library cache entries for non-existent objects are relatively small, so heavy load of them may be the reason of shared pool fragmentation

Also consider these factors as the systems is hanging and no error messages are seen.

For the benefit of the other, here is the outcome:

I tried to flush the shared pool, that did not solve the problem.

Oracle support was not able to help, we did see a "library cache lock" by doing "alter session set events '10046 trace name context forever, level 12'". But were not able to see the locking in v$lock...so...

Fortunately & Unfortunately recycling the database did the trick.

The PUBLIC synonym issue has been resolved.

[Edited by khussain on 01-09-2002 at 09:17 AM]

Quote:

---

Originally posted by khussain
For the benefit of the other:
Oracle support was not able to help

---

I guess no one is surprised !