Xref: alice comp.databases.oracle.tools:26841
Path: alice!news-feed.fnsi.net!newspump.monmouth.com!newspeer.monmouth.com!newsfeed1.earthlink.net!nntp.earthlink.net!posted-from-earthlink!not-for-mail
From: "Kenny Lim" <kennylim@earthlink.net>
Newsgroups: comp.databases.oracle.tools
Subject: Newbie - How can I reference to a table using a synonyms via dblinks to a remote server ?
Date: Fri, 18 Jun 1999 23:10:43 -0700
X-Posted-Path-Was: not-for-mail
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-ELN-Date: 19 Jun 1999 06:11:29 GMT
X-ELN-Insert-Date: Fri Jun 18 23:15:10 1999
Organization: EarthLink Network, Inc.
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MSMail-Priority: Normal
Lines: 86
Message-ID: <7kfcah$d68$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@LYN_DBLINK1.WORLD;


Any advise or pointers will be greatly appreciated.

Thanks in advance.

Kenny -

NO SPAMMING PLEASE !