urgent help

[sathya1212]

hi..guys........
i have 2 problems

1) i have 2 databases test(8.1.6) and test1(8.1.5)
i able to acess test1 from test through a database link
but the other way is not possible. i get the error ora-12154.

2) from test db(user scott) i was able to create a link to scott in test1 db.now,i created a synonym for emp in scott(in test1 db) in test db.i want to access this synonym from some other user(say blake) in test db.i cannot create public synonym(no permission).so when i try to grant select on synonym to that user i get
ORA-02021: DDL operations are not allowed on a remote database.also i cannot create a link from blake to scott(in test1 db).
what is the alternate solution

thank u

[gnagesh]

Hi Satya,

For your Q1, It is quite possible to connect in both the ways. I too have same environment, and I tested and it was successful. I am suspecting there is a problem with your tnsnames.ora, please check.

--Nagesh

[reydp]

for q2: the database link that you have created should be PUBLIC.
create public database link DBLINKNAME connect to USER identified by PASSWORD using 'TNSNAME';

hope that helps.

[sathya1212]

hi...guys thanx.........

for q2 it did not work with public database link and also with public synonym.i tried it....
any other solutions

[reydp]

in test:
ask your dba to allow you to create a public database link:
GRANT CREATE PUBLIC DATABASE LINK TO SCOTT;
in test1:
since you use SCOTT to connect to the db link, then all objects
that SCOTT can access, are the only objects visible to TEST db using the database link. So if you need other table to be accessible from TEST db, then you need to create a synonym and then grant select privs to SCOTT.
CONNECT USER2/USER2@test1
create synonym syn_tabname for tabname;
grant select on syn_tabname to scott;

in test:
Since you have created a public database link to SCOTT, and scott was able to create pubic database link successfully, then blake can also view the objects where SCOTT(test1) can also access or owned.

[sathya1212]

hi...i got u...

but the problem is ...
i was able to create public database link in test ,then i was able to create synonym for emp table in scott(of test1).
no probs.but, when i try to grant select priviledge on synonym to blake i get the
ORA-02021: DDL operations are not allowed on a remote database error.
but,it is working if i create a public synonym on emp in scott(of test)...
but,i do not have create public synonym in production evn.
any other solution

[reydp]

is blake a user of test or test1?
if blake is in test db and you are trying to grant select privs of synonym from test1 to blake, then I say it's not possible.

[sathya1212]

yes..now u got it
blake is in test db.....and i am trying to grant select priv on the synonym in test1 db to him...thats why i was getting the error....

but,do u have any alternate soultion other than the public synonym and view option

[reydp]

why not create another database link but this time owned by BLAKE with the same CONNECT, USING option?

[sathya1212]

actually..the prob is sorted by creating a view...
but,my worry is if the view becomes invalidated....
then the maintainence will become very tough...
thats why i was searching for alternate solution...
i cannot create a link from blake coz i do not have the permission.....

kindly let me know if u get any alternate solution

anyway thank u very much