| DB link [message #23687] |
Fri, 27 December 2002 13:49  |
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  |
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 |
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 |
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 |
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 |
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 |
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 #255588 is a reply to message #255572] |
Wed, 01 August 2007 04:30 |
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 [message #260041 is a reply to message #260030] |
Fri, 17 August 2007 04:31 |
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 #260235 is a reply to message #260225] |
Fri, 17 August 2007 14:26 |
 |
Barbara Boehmer
Messages: 9100
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 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
