I have 3 databases. 1 links to 2, 2 links to 3. I'd like to query tables in 3, from 1. I tried third_db_tab@3@2 and it did not work. Wondering if this is possible and if so, what the syntax is.
+5
A:
I think you can do this by creating synonyms. In database 2 create a synonym:
CREATE SYNONYM third_db_tab for third_db_tab@3;
then in database 1 create a second synonym:
CREATE SYNONYM third_db_tab for third_db_tab@2;
This is untested (I don't have three databases running right now), but I think Oracle is smart enough to untangle the synonyms.
Thomas Jones-Low
2010-06-07 13:38:27
When this kind of question comes up and doesn't get accepted it is usually because the developer does not have a good relationship with their Database Administrator and are looking for an answer that avoids having to speak with their Database Administrator. The real answer to this question should almost be "Take your DBA to lunch and learn his or her kids names and find out what his favorite baseball team is". Then say thank you when he/she creates the synonyms for you.
Brian
2010-06-07 13:44:03
I just tested this, it does work.
Dave Costa
2010-06-07 13:47:07
@Brian You are correct. This difficulty is unfortunately as a result of our organizational structure more so than of my personal relationship with my co-workers. I will accept this answer since Dave tested it and it does technically solve the problem, despite my aversion of synonyms.
RenderIn
2010-06-07 14:15:37
@Renderln I understand the bad use of synonyms can confuse, hide or distort, I have found there are a few problems like this one that can be more easily, or only, solved through the use of synonyms.
Thomas Jones-Low
2010-06-07 14:42:20