OraFAQ Forum: Performance Tuning » Synonyms and dblinks

Home » RDBMS Server » Performance Tuning » Synonyms and dblinks (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Synonyms and dblinks [message #360329]

Thu, 20 November 2008 07:36

Srinandini
Messages: 13
Registered: November 2008

Junior Member

Hi All

I have 2 databases say A and B
I have a few tables in database A that do not exist in databse B

But my Pl/SQL package has to be run across both these databases

So in database A

i created a public synonym for table T1 using the cmd below

create public synonym T1 for user1.T1@link1
(pls note that dblinks are already available)

Now if i try to access the table T1 in database B , i should not face any errors. Am i correct?

Please let me know if my understanding reg synonyms and dblinks is correct

Thanks in advance
Srinandini

Report message to a moderator

Re: Synonyms and dblinks [message #360408 is a reply to message #360329]

Thu, 20 November 2008 21:31

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Quote:

I have a few tables in database A that do not exist in databse B

If your extra tables are in A then you put the synonym in the other database, but you are essentially correct in that your approach is sound.

Remember though, that there are some rules behind distributed transaactions. This means that almost all code (99%) you run on one database will work in a distributed environment as well where objects are spread out across multiple databases and you use synonyms to make it look like everything is still local.

But if you start doing complex or advanced stuff, you can hit issues. These usually manifest as ora-600 errors, or sometimes legitimate restrictions on distributed transactions. Don't ask me for a list please, I don't have one.

Good luck, Kevin

Report message to a moderator

Re: Synonyms and dblinks [message #360410 is a reply to message #360408]

Thu, 20 November 2008 21:55

Srinandini
Messages: 13
Registered: November 2008

Junior Member

Hi Kevin

thank you so much for the reply

for the table T1 which is in database A , if i try to access the table in databse B , am facing the problem

object does not exist
and other error is ora-600

Just wanted to know if my approach is correct or am going wrong anywhere.

thanks
Srinandini

Report message to a moderator

Re: Synonyms and dblinks [message #360415 is a reply to message #360410]

Thu, 20 November 2008 22:12

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Nine times out of ten, "OBJECT DOES NOT EXIST" is a privileges issue. A database link logs onto a user at the remote site. That user must have privileges for whatever operations you are performaning on the objects at the remote site.

ORA-600 errors are harder to figure out. You must break down your code into steps till you find the offending step that is causing the error. Then you at least have the option to re-write that step or change you solution design to avoid the coding construct(s) that are causing the ora-600. You might also wish to file a bug report with Oracle. This might go well or badly depending on existence of a patch for you problem or not.

There are many many patches for each release of Oracle. Oracle is not in the habit of giving every patch to every customer because most of these patches are one offs for a verify specific scenario. Thus they wait for a customer to report the associated problem. Only after reporting the associated problem will you be made aware of the existence of a patch for it. It can therefor help to contact them for an ora-600. But it is often just as true that there is no patcn and it will take some time to fix.

Good luck, Kevin

Report message to a moderator

Re: Synonyms and dblinks [message #360583 is a reply to message #360415]

Fri, 21 November 2008 08:21

Srinandini
Messages: 13
Registered: November 2008

Junior Member

Kevin,

Thanks a lot. Error got resolved

Smile

thanks
Srinandini

Report message to a moderator

Previous Topic:	ORA-12013: updatable materialized views must be simple enough to do fast refresh
Next Topic:	Oracle Tuning - Explain plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 00:57:20 CDT 2024