DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2000
    Chicago, IL
    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 ???

  2. #2
    Join Date
    Oct 2000
    Saskatoon, SK, Canada
    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

             CREATE PUBLIC SYNONYM synonym_name
             FOR schema.object_name;

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

    Life is a journey, not a destination!

  3. #3
    Join Date
    Sep 2000
    Chicago, IL
    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 ?

  4. #4
    Join Date
    Sep 2001
    Fort Smith
    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.

  5. #5
    Join Date
    Sep 2000
    Chicago, IL
    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]

  6. #6
    Join Date
    Oct 2001
    Originally posted by khussain
    For the benefit of the other:
    Oracle support was not able to help
    I guess no one is surprised !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.