Home » RDBMS Server » Performance Tuning » Synonyms and dblinks (Oracle 9i)
Synonyms and dblinks [message #360329] Thu, 20 November 2008 07:36 Go to next message
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
Re: Synonyms and dblinks [message #360408 is a reply to message #360329] Thu, 20 November 2008 21:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Synonyms and dblinks [message #360410 is a reply to message #360408] Thu, 20 November 2008 21:55 Go to previous messageGo to next message
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
Re: Synonyms and dblinks [message #360415 is a reply to message #360410] Thu, 20 November 2008 22:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Synonyms and dblinks [message #360583 is a reply to message #360415] Fri, 21 November 2008 08:21 Go to previous message
Srinandini
Messages: 13
Registered: November 2008
Junior Member
Kevin,

Thanks a lot. Error got resolved

Smile

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


Current Time: Thu Dec 08 08:39:05 CST 2016

Total time taken to generate the page: 0.08895 seconds