"Drop Public Synonym" hangs
Bobs...@gmail.com
We have a public synonym on 10g (10.2.0.3.0) that uses a DB_Link to an
8i instance.
When trying to do anything with the synonym, there is no response - no
error - no results - hangs for a very long time.
Attempted things done that all hang:
Select * from our_synonym;
drop public synonym our_synonym FORCE;
create or replace public synonym our_synonym for some_other_table;
Any ideas on why this is happening and what the remedy is?
Thanks
Bob
Shakespeare
<Bobs...@gmail.com> schreef in bericht
news:f2ad0b04-36e2-4f26...@25g2000hsx.googlegroups.com...
Maybe if you tell us the exact name of our_synonym.
IIRC, you can't db-link from 10g to 8i directly (not sure about that though)
Shakespeare
ora...@msn.com
> <BobsGT...@gmail.com> schreef in berichtnews:f2ad0b04-36e2-4f26...@25g2000hsx.googlegroups.com...
>
> - Show quoted text -
You can if the 8i is 8.1.7.4; nothing earlier than that, though.
David Fitzjarrell
Bobs...@gmail.com
> Maybe if you tell us the exact name of our_synonym.
>
> IIRC, you can't db-link from 10g to 8i directly (not sure about that though)
>
> Shakespeare
1. You can link (we are using 8.1.7.4 - as correctly suggested by
Fitz).
2. How would my synonym name help you to respond?
3. Maybe I am cranky now (late afternoon) - but why did you bother
responding with no info?
Shakespeare
<Bobs...@gmail.com> schreef in bericht
news:30a5643e-b5f1-4d4e...@m44g2000hsc.googlegroups.com...
1) I told you I was not sure, and Fitz corrected it correctly. If you had
bothered to state your version in your question, I might have looked it up
before answering.
2) You would be surprised how many people tried to drop a synonym which
appeared to be a reserved word or something alike... and put a question on a
forum
3) OK. I'll put you on my ignore list if you like
Shakespeare
ora...@msn.com
On Aug 5, 3:38 pm, BobsGT...@gmail.com wrote:
> > Maybe if you tell us the exact name of our_synonym.
>
> > IIRC, you can't db-link from 10g to 8i directly (not sure about that though)
>
> > Shakespeare
>
> 1. You can link (we are using 8.1.7.4 - as correctly suggested by
> Fitz).
It wasn't a suggestion, it was a statement of fact.
> 2. How would my synonym name help you to respond?
It may be that you've decided to choose a name for your synonym listed
in V$RESERVED_WORDS; not all of those entries throw an error when used
for other purposes.
I understand that this next request may appear strange, but does the
following query return in a timely manner:
select distinct owner
from v$access;
If not then the issue *may* revolve around fixed object statistics;
executing dbms_stats.gather_fixed_objects_stats(NULL) *may* fix your
situation. Then, again, it may not fix anything. Offering advice to
one who cannot, or will not, provide sufficient information about the
problem is difficult, at best.
> 3. Maybe I am cranky now (late afternoon) - but why did you bother
> responding with no info?
Possibly because you provided so little with which to work in your
original post and in this follow-up.
David Fitzjarrell
Bobs...@gmail.com
> > 3. Maybe I am cranky now (late afternoon) - but why did you bother
> > responding with no info?
>
> Possibly because you provided so little with which to work in your
> original post and in this follow-up.
Okay - sorry for being cranky. Anyway - the problem is this - we have
circular links - a public synonym on both 8i and 10g pointing to each
other through reverse database links. Now, I just got on this project
and I did not set it up. However, a post on the internet from "Neil
Jarvis" indicates that in this situation, once the synonym is used
through the link, it will just hang every other DDL/DML statement
related to the public synonym and not return with an error or
anything. This is what we are seeing and he provides a solution which
I am going to try.
See link "Bugs, Quirks and Features" on the following link (it is a
word document).
http://www.go-faster.co.uk/bugs.htm
Shakespeare
<Bobs...@gmail.com> schreef in bericht
news:6cde2dda-724a-42cc...@k30g2000hse.googlegroups.com...
>
>> > 3. Maybe I am cranky now (late afternoon) - but why did you bother
>> > responding with no info?
>>
>> Possibly because you provided so little with which to work in your
>> original post and in this follow-up.
>
>
> Okay - sorry for being cranky.
Apologies granted.
> Anyway - the problem is this - we have
> circular links - a public synonym on both 8i and 10g pointing to each
> other through reverse database links. Now, I just got on this project
> and I did not set it up. However, a post on the internet from "Neil
> Jarvis" indicates that in this situation, once the synonym is used
> through the link, it will just hang every other DDL/DML statement
> related to the public synonym and not return with an error or
> anything. This is what we are seeing and he provides a solution which
> I am going to try.
>
> See link "Bugs, Quirks and Features" on the following link (it is a
> word document).
> http://www.go-faster.co.uk/bugs.htm
Interesting link, thanks.
Shakespeare
Bobs...@gmail.com
The suggestion in the link worked.
After alerting developers to beware, I killed all sessions that were
coming through a DB_Link
select * from v$session where username='Our_Database_Link';
Then I was able to drop the public synonym that was causing all the
trouble and recreate the synonyms properly.
Everyone is happy and up and running.
Bob