Home » SQL & PL/SQL » SQL & PL/SQL » DB link
DB link [message #23687] Fri, 27 December 2002 13:49 Go to next message
Raj
Messages: 411
Registered: November 1998
Senior Member
CREATE DATABASE LINK dblink1
CONNECT TO cadmin IDENTIFIED BY softpro
USING 'mip';

DB Link is created.

when i give this,
select * from testdblink@dblink1

i get
"ORA12154:TNS could not resolve service name."

i'm able to connect to both databases and fetch data without any problem.

Say these are my 2 diff databases

DB name: dbone
User id : UIDONE
Password : PWDONE

DB name: dbtwo
User id : UIDTWO
Password : PWDTWO

Now i'm connecting to dbone..connected..Now i execute

CREATE DATABASE LINK dblink1
CONNECT TO dbtwo IDENTIFIED BY UIDTWO
USING 'PWDTWO';

is this statement right ?

[Updated on: Wed, 01 August 2007 08:05] by Moderator

Report message to a moderator

Re: DB link - Very Very Urgent [message #23689 is a reply to message #23687] Fri, 27 December 2002 15:11 Go to previous messageGo to next message
Sesh
Messages: 2
Registered: December 2002
Junior Member
Hi

try using the public in the script like

CREATE public DATABASE LINK dblink1
CONNECT TO cadmin IDENTIFIED BY softpro
USING 'dblink1';

it shud work !

--sesh
Re: DB link - Very Very Urgent [message #23691 is a reply to message #23687] Fri, 27 December 2002 18:55 Go to previous messageGo to next message
Amit Chauhan
Messages: 74
Registered: July 1999
Member
Hi,
The only problem I can see is that its not able to find the connect string mip. And it checks for this connect string in the tnsnames.ora file on the server. Are you able to log on to the database using this connect string (mip) from the machine where the oracle is installed using sqlplus ? Make sure you this from the server machine, not your local machine, so that sqlplus picks up the tnsnames.ora file from the server (which this db link must be picking up).

HTH
Amit
Re: DB link - Very Very Urgent [message #23698 is a reply to message #23691] Sat, 28 December 2002 11:09 Go to previous messageGo to next message
Raj
Messages: 411
Registered: November 1998
Senior Member
Our two databases are in remote places and i'm connecting both of them from my local machine.
i have configures my tnsnames.ora file, i'm able to connect both the databases.now i'm trying to make dblink from one of the databases to the other.

cant i make dblink from client machine? should i run only from machime where db resides ? i'm very new to oracle.kinldy guide me in simple steps.
Re: DB link - Very Very Urgent [message #23701 is a reply to message #23691] Sun, 29 December 2002 01:19 Go to previous messageGo to next message
Amit Chauhan
Messages: 74
Registered: July 1999
Member
Hi,
When you create a db link, and you specify the connect string, which in ur case is mip, then this connect string should be defined in the tnsnames.ora file on the server where the oracle is installed. This is because after you define the db link, then its a part of the database, and stores no information of your client machine. Different clinet machines can have different connect strings defined that connect to the same database. If you define a db link, then this should be accessible from any machine. Thats why it uses the connect string defined on the server machine and not on each clinet machine.
Create the db link that uses the connect string which is defined on the tnsnames.ora file on the server, and not the one on ur client machine.

Hope that helps
Thanks
Amit
Re: DB link - Very Very Urgent [message #23732 is a reply to message #23687] Tue, 31 December 2002 12:07 Go to previous messageGo to next message
andy
Messages: 92
Registered: December 1999
Member
Also note that depending on the OS and Oracle server version you are running, the default domain behaves differently. You should probably change the create dblink command to:

CREATE DATABASE LINK dblink1
CONNECT TO cadmin IDENTIFIED BY softpro
USING 'mip.world';

and change the alias in the tnsnames.ora file on the server to be mip.world. To be on the safe side, you might also want to make sure to set NAMES.DEFAULT_DOMAIN = world in the sqlnet.ora file on the server.

HTH Good luck!
Andy
Re: DB link - Very Very Urgent [message #255570 is a reply to message #23732] Wed, 01 August 2007 04:10 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
I am trying to create DB Link from CUSMC Database to cupod database
i am getting the following error,please advice.


SQL> create public database link dblin connect to stage identified by stage using 'cupod';

Database link created.

SQL> select * from payor_center@dblin;
select * from payor_center@dblin
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


My TNS File


CUSMC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chn82690.ad.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CUSMC)
)
)

CUSAP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chn82690.ad.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CUSAP)
)
)

CUPOD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chn82690.ad.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CUPOD.ad.company.com)
)
)
Re: DB link - Very Very Urgent [message #255572 is a reply to message #23732] Wed, 01 August 2007 04:13 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Are you able to TNSPING CUPOD instance? check first whether you are able to connect to CUPOD directly or not? DB_LINK will get created irrespective of whether you are able to connect or not?

Regards
Himanshu
Re: DB link - Very Very Urgent [message #255573 is a reply to message #255570] Wed, 01 August 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is it your client or server tnsnames.ora?
What is you sqlnet.ora? Does it contain a default domain?

Regards
Michel
Re: DB link - Very Very Urgent [message #255588 is a reply to message #255572] Wed, 01 August 2007 04:30 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yeah the TNSNAMES.ORA should be server side, not client side..

otherwise try creating like this -

create public database link dblin connect to stage identified by stage using 
'(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = chn82690.ad.company.com)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = CUPOD.ad.company.com)))'

[Updated on: Wed, 01 August 2007 05:01] by Moderator

Report message to a moderator

Re: DB link - Very Very Urgent [message #255595 is a reply to message #255588] Wed, 01 August 2007 04:37 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
its my client tnsnames.ora,and am creating it from the client system
Re: DB link - Very Very Urgent [message #255598 is a reply to message #255595] Wed, 01 August 2007 04:40 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Thanks himang,it worked
Re: DB link - Very Very Urgent [message #255599 is a reply to message #23687] Wed, 01 August 2007 04:40 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

the entry has to be in server side tnsnames.ora of the database where you are creating the DB_LINK. Try with the option I have given, it should work....
Re: DB link - DB_LINK for an user through SYS login [message #260012 is a reply to message #23687] Fri, 17 August 2007 02:46 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Is it possible to create a DB_LINK for a user through SYS login? I don’t want to create a public database link, but link for a user through the SYS login.. is it possible?
Re: DB link - DB_LINK for an user through SYS login [message #260018 is a reply to message #260012] Fri, 17 August 2007 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what you want to do but never do something with/through SYS.
This is a very bad idea and not supported.

Regards
Michel

[Updated on: Fri, 17 August 2007 04:44]

Report message to a moderator

Re: DB link [message #260030 is a reply to message #23687] Fri, 17 August 2007 03:46 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

The problem is I don't have that username's (on which the DB_LINK needs to be created) password and don't want to modify the password either. So just want to know if it is possible to create db_link through SYS or through any user having DBA access...

Other option I have, is to modify the password temporarily and revert it back to original values after creating the DB_LINK.

Regards
Himanshu
Re: DB link [message #260041 is a reply to message #260030] Fri, 17 August 2007 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Modifying the pwd and changing it back will not work, as you need the current password in order to create a session as that user on the remote database.

Creating a Db link to SYS is a REALLY bad idea.
Re: DB link [message #260062 is a reply to message #23687] Fri, 17 August 2007 05:47 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I think I didn't made myself clear...

I have username/password for the remote database. I don't have the user's password for the database on which I am creating the database link.

What I need to do is to login as SYS on my database and want to create a db_link for USER A on my database, pointing to normal account user on remote database.

======
Database X
username - A
password - unknown

Database Y (Remote)
username - B
Password - known
========

Create a db_link on Database X pointing to User B on Database Y. As I don't have password for user A, so I need to login as SYS and create a db_link for username A by altering the session to user A.

Connect to SYS on database X

ALTER SESSION SET CURRENT_SCHEMA = A;

CREATE DATABASE LINK db_test.world
CONNECT TO B
IDENTIFIED BY "known"
USING 'DATABASE Y';



This fails with unsufficient privileges error.... Any help??
Re: DB link [message #260066 is a reply to message #260062] Fri, 17 August 2007 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't have the password to connect to user A, are you sure you are allowed to do what you wanted?
Are you asking us to help you in an illegal act?

Regards
Michel
Re: DB link [message #260074 is a reply to message #23687] Fri, 17 August 2007 06:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Its not a problem of password as I have SYS access on the database. I can in any case reset the password and do the stuff... only thing is I don't want to reset the password of the user as some dependent applications are using that password in encrypted format...

Just wanna know if it can be created through the SYS account...

Regards
Himanshu
Re: DB link [message #260084 is a reply to message #260074] Fri, 17 August 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are allowed to access the schema, why don't you have the password?
If you don't have and can't get the password then it seems you don't have a legal access.

No, you can't do it with anything but the schema.
Full stop.

Regards
Michel
Re: DB link [message #260212 is a reply to message #260074] Fri, 17 August 2007 13:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It sounds to me like what you want to do is legitimate. You want to create a database link for one of your users. You could easily do this by altering the user's password, but that would inconvenience the user, so you want to do it without permanently altering the user's password. The following script from Tom Kyte will allow you to store the user's hashed password, change the password temporarily, login using the temporary password, then reset the password to the original using the stored hashed value.


http://asktom.oracle.com/tkyte/Misc/su.html
Re: DB link [message #260225 is a reply to message #260212] Fri, 17 August 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara,

I wanted to be sure this is a legitimate use before giving such a solution as it also can be used as a hack.

If it was such a simple case that "I have a script to create all objects of a user", I think the OP could write it.
I maintain that if you have to create a database link (and just this) for a user either the user can do it (after all, he has the privilege) either he delegate you to do it and give you the password to connect and do it for him.
It seems to me... weird that OP has the password for the remote user and not for the local one...

Regards
Michel
Re: DB link [message #260235 is a reply to message #260225] Fri, 17 August 2007 14:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Fri, 17 August 2007 11:47
It seems to me... weird that OP has the password for the remote user and not for the local one...



You find it weird that somebody with sys privileges, like a DBA, does not have a user's password? I would find it weird if a DBA did have a list of passwords. The obvious alternatives are that he could ask the user for the password or change the password and give the new one to the user, but he should not have to do these. In addition to the inconveniences, there may be other things affected. For the other alternative of the user creating the database link, he would have to give the user the remote password, which he may not want to do. I have been in a similar situation before, where I was working at night and needed such a link, but was prohibited from creating it myself, and the DBA worked during the day. If Tom Kyte thinks there is a legitimate need, that is good enough for me. Any place that gives someone a sys password should understand that it gives them access to everything. It is not our responsibility to ensure that other people's systems are secure or that they give passwords to appropriate people. I don't think there is any harm or liability in providing a link to legitimate code that a vice-president of Oracle corporation (Tom Kyte) has made publicly available on the internet.
Re: DB link [message #260238 is a reply to message #260235] Fri, 17 August 2007 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You find it weird that somebody with sys privileges, like a DBA, does not have a user's password?

No, I find weird that he can't get it from the user.
(And I find weird that he used SYS to a user maintenance task and not a DBA account.)
Quote:
I would find it weird if a DBA did have a list of passwords

I absolutly agree.
Quote:
he would have to give the user the remote password, which he may not want to do

Good point! So we come back to the first way: ask the user his password (maybe a temporary one, I imagine creating a database link is not a thing you do on the fly but part of a delivery).
Quote:
if Tom Kyte thinks there is a legitimate need, that is good enough for me.

This script (or the like) existed years before AskTom existed, It was useful in pre-9i version when nobody can grant object privileges but the owner himself. This was the reason of its existence.
Quote:
I don't think there is any harm or liability in providing a link to legitimate code that a vice-president of Oracle corporation (Tom Kyte) has made publicly available on the internet.

There are things on first Tom's site that he'd like to erase but can't. His mind and experience change with years. For instance the article he wrote "On update cascade", now he says that he hates triggers and asks every one to not use them instead of functional code.
As I said, I surely provided the same if I was sure the OP want to use it in a legitimate case.

Regards
Michel
Re: DB link [message #260257 is a reply to message #260238] Fri, 17 August 2007 16:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It was back in the year 2000 when I first saw that script posted by Tom Kyte and there are still threads on his site with links to it. I find it hard to believe that he cannot delete things on his own site. I am sure he could at least edit them.
Re: DB link [message #260284 is a reply to message #260257] Sat, 18 August 2007 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, he can delete them but he does not want.
He already said to someone that asked him about some posts in AskTom that he doesn't ever delete or edit anything in his site, even his errors.
A great lesson!

Regards
Michel
Re: DB link [message #260466 is a reply to message #260212] Mon, 20 August 2007 01:16 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Barbara Boehmer wrote on Fri, 17 August 2007 23:58
It sounds to me like what you want to do is legitimate. You want to create a database link for one of your users. You could easily do this by altering the user's password, but that would inconvenience the user, so you want to do it without permanently altering the user's password. The following script from Tom Kyte will allow you to store the user's hashed password, change the password temporarily, login using the temporary password, then reset the password to the original using the stored hashed value.


http://asktom.oracle.com/tkyte/Misc/su.html


Thanks Barbara,
Thats exactly how I was planning to do but wanted to check if it was possible other way Smile

Anyway, thanks experts for your comments...

Regards
Himanshu
Re: DB link [message #261473 is a reply to message #260466] Wed, 22 August 2007 12:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I asked Tom Kyte about this and he provided another method:


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058#408884400346292168
Re: DB link [message #261475 is a reply to message #261473] Wed, 22 August 2007 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback. For your information, I also answered in AskTom.

I provided a (quite long) example of how to use the method indicated by Tom in the topic Multi-tier auditing in Oracle from Business Objects if you want to have a look. The purpose was not the same but it shows an abstract of what can be done.

Regards
Michel

Re: DB link - Very Very Urgent [message #275293 is a reply to message #255588] Fri, 19 October 2007 04:46 Go to previous message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
Himang,

Thanks a lot. I just found this thread and it worked.

Regards,
Prabhu
Previous Topic: PL/SQL Tuning (merged 2 threads)
Next Topic: plsql
Goto Forum:
  


Current Time: Fri Dec 09 00:21:12 CST 2016

Total time taken to generate the page: 0.14516 seconds