Newbies - How can I reference to a table using a synonyms via dblink to a remote server ?

From: Kenny Lim <kennylim_at_earthlink.net>
Date: Fri, 18 Jun 1999 23:08:21 -0700
Message-ID: <7kfc65$ctl$1@birch.prod.itd.earthlink.net>

Hi Guys,

I am experimenting in how to query a table using a synonyms via dblink referencing to a remote server.

First I created a simple table in instance "A" Then I created a dblink and synonyms referencing to the table in instance "B"
Upon creating the neccesaries, I try to perform a query against the synonyms in instance "B",

I received the following error messages :

ORA-02085: database link name connects to name

My sid name is "orcl" as default and the service name is "oracle_A" in instance A.

--CREATE TABLE IN INSTANCE "A"
CREATE TABLE KENNYL.LYN_TABLE1
(

    COL1 VARCHAR2(25) NULL
)
TABLESPACE USR
LOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 50K

        NEXT 50K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 1
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

NOPARALLEL
NOCACHE;
GRANT ALTER ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT DELETE ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT INDEX ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT INSERT ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT REFERENCES ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT SELECT ON KENNYL.LYN_TABLE1 TO PUBLIC; GRANT UPDATE ON KENNYL.LYN_TABLE1 TO PUBLIC;
--INSERT A ROW OF TEST DATA
INSERT INTO KENNYL.LYN_TABLE1(COL1)

                  VALUES('THIS IS JUST A TEST');

--CREATE DBLINKS IN INSTANCE "B"
CREATE PUBLIC DATABASE LINK LYN_DBLINK1.WORLD CONNECT TO KENNYL IDENTIFIED BY KENNYL USING 'oracle_A';

--CREATE SYNONYM IN INSTANCE "B"
CREATE PUBLIC SYNONYM LYN_SYNONYM
    FOR KENNYL.LYN_TABLE1_at_LYN_DBLINK1.WORLD; Any advise or pointers will be greatly appreciated.

Thanks in advance.

Kenny -

NO SPAMMING PLEASE ! Received on Sat Jun 19 1999 - 01:08:21 CDT