Home » RDBMS Server » Server Administration » Problem in creating database link
Problem in creating database link [message #230106] Tue, 10 April 2007 22:51 Go to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi frnds,

I have a database which has following users:

user1 ,user2

user1 will be refreshed daily.As i need to access tables in user1 from user2 i m trying to create a datbase link in user2 to access the tables in user1 instead of granting objects to user2 from user1.I am getting the following error when trying to create a datbaselink in user2

Link : "LNK_DEL_CKNG"
Error : ORA-12154: TNS:could not resolve the connect identifier specified


I am wondering why this is happening though the users are in same machine.When i am trying to create a db link from a user which is my local it is successful Sad..

Please let me know what could be the issue.

Immediate help would be appreciated.

Thanks.
Re: Problem in creating database link [message #230108 is a reply to message #230106] Tue, 10 April 2007 22:55 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,
What is your service_name/SID ?

create database link db_link_name
connect to <username> identified by <pwd>
using 'SERVICE_NAME';


NOte : service_name is target database service_name /SID.

Sorry too fast .. i didn't read complete question.

@OP>Purpose

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database.

follow "anacedent" suggestion.

Regards
Taj

[Updated on: Tue, 10 April 2007 23:00]

Report message to a moderator

Re: Problem in creating database link [message #230109 is a reply to message #230106] Tue, 10 April 2007 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
IMO, you are using the wrong "tool" for the job.
IIRC, one can not create a DBLINK back to the originating host.
I suggest you consider using a ROLE & issue the necessary GRANTs to this ROLE.
If you insist in pursuing this folly then stop describing what you think you are doing.
Use full, complete CUT & PASTE of whole session so everyone can see EXACTLY what is being given to Oracle & how it actually responds.
Re: Problem in creating database link [message #230110 is a reply to message #230106] Tue, 10 April 2007 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>NOte : service_name is target database service_name /SID.
Correction? the string between the single quote marks in CREATE DATABASE LINK statement is the TNSNAMES.ORA alias entry & does NOT have to match either SERVICE_NAME or SID.
This alias is the string to the left of the first equal sign which starts the entry for a single instance.
Re: Problem in creating database link [message #230111 is a reply to message #230110] Tue, 10 April 2007 23:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
CREATE DATABASE LINK statement is the TNSNAMES.ORA alias entry & does NOT have to match either SERVICE_NAME or SID.
This alias is the string to the left of the first equal sign which starts the entry for a single instance.

Correct.
but possibly service_name in tnsnames.ora is equal to SID.
but Net Service Name is not sure is some as oracle sid.

HGC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.9)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oramfe)
    )
  )



My db_name/SID is >>> ORAMFE
and Net Service Name >>> HGC

Anyway thank you.

Regards
Taj
Re: Problem in creating database link [message #230113 is a reply to message #230108] Tue, 10 April 2007 23:11 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi ,

Thanks for the immediate response.

Well i am able to create the public database link as follows;

SQL> CREATE PUBLIC DATABASE LINK "lnk_user2_user1" CONNECT TO user1 IDENTIFIED BY "user1" USING 'start';

Database link created.

but when i am tring to access table from user1 from user2
as follows

SQL>select * from @lnk_user2_user1;

ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


Any help on this plz.

Thanks in advance
Re: Problem in creating database link [message #230116 is a reply to message #230106] Tue, 10 April 2007 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post the complete contents of tnsnames.ora file.
Re: Problem in creating database link [message #230141 is a reply to message #230106] Wed, 11 April 2007 00:59 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi,

Here is my tnsnames.ora file


START.ORACLE.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = stora01)(PORT = 1521))
(CONNECT DATA =
(SID = START)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


Thanks in Advance
Re: Problem in creating database link [message #230144 is a reply to message #230141] Wed, 11 April 2007 01:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could it be that TNSNAMES.ORA on a server does not contain alias to the database you'd like to create a database link to?

Because, if you have DB alias in TNSNAMES.ORA on your client computer (your PC), you'll be able to connect to the remote database through SQL*Plus or Developer Suite or ..., but you will not be able to create a database link to it UNLESS it is in a TNSNAMES.ORA on Oracle server.
Re: Problem in creating database link [message #230148 is a reply to message #230106] Wed, 11 April 2007 01:22 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi,

That can be a problem.But when the users are in the same database and when i m trying to create the link the same machine ,the link is getting created bt not working .Am i missing something ?

Thanks

Re: Problem in creating database link [message #230317 is a reply to message #230106] Wed, 11 April 2007 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
CREATE PUBLIC DATABASE LINK "lnk_user2_user1" CONNECT TO user1 IDENTIFIED BY "user1" USING 'start';

Provide CUT & PASTE results from both of these commands:

$ SQLPLUS USER1/"user1"@START

$ SQLPLUS USER1/"user1"@START.ORACLE.COM
Re: Problem in creating database link [message #230322 is a reply to message #230148] Wed, 11 April 2007 09:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
the users are in the same database


Database link is not your solution.

Database link used for two database, not for two users.

Regards
Taj
Re: Problem in creating database link [message #230323 is a reply to message #230106] Wed, 11 April 2007 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Database link is not your solution.
This was my conclusion hours ago:
Problem in creating database link [message #230109 is a reply to message #230106 ] Tue, 10 April 2007 20:58
Re: Problem in creating database link [message #230328 is a reply to message #230323] Wed, 11 April 2007 09:37 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Agree. I already point in my FIRST post on this thread.

Regards
Taj

[Updated on: Wed, 11 April 2007 11:31]

Report message to a moderator

Re: Problem in creating database link [message #230348 is a reply to message #230328] Wed, 11 April 2007 11:19 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Taj you are correct, Solution is you have to grant respective privilege to users, so that can access to the respective objects.

Re: Problem in creating database link [message #230351 is a reply to message #230106] Wed, 11 April 2007 11:30 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi frnds,

I do agree with you Taj,the only concerns i have is if i grant select,update on some objects to the user2 and if the user1 is reloaded will the user2 will be able to still access the objects.I was trying to do that but couldn't.I may be doing it in wrong way..So please give me the right direction.

I am asking you this question because we have a nightly job which will referesh the user1.

Thanks to anacedent,harshad for the replies.

Thanks in advance
Re: Problem in creating database link [message #230353 is a reply to message #230351] Wed, 11 April 2007 11:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
if the user1 is reloaded will the user2 will be able to still access the objects.

SQL> show user
USER is "TAJ"
SQL> create table test1 as select * from all_objects;

Table created.

SQL> alter user scott account unlock;

User altered.

SQL> grant select , insert, update , delete on TEST1 to SCOTT;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create synonym taj_test1 for TAJ.TEST1;

Synonym created.

SQL> create table test1 as select * from taj_test1;

Table created.

SQL> conn taj/taj
Connected.
SQL> drop table test1;

Table dropped.

SQL> select count(*) from SCOTT.TAJ_TEST1;
select count(*) from SCOTT.TAJ_TEST1
                           *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> create table test1 as select * from all_objects;

Table created.

SQL> select count(*) from SCOTT.TAJ_TEST1;

  COUNT(*)
----------
     47570


For above example Reloaded is RECREATE objects.
if you want something else please give us more details.
Re: Problem in creating database link [message #230354 is a reply to message #230351] Wed, 11 April 2007 11:39 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Hi ,

AFAIK it should be fine, it would not make any issue, I hope reloading user is not changes tables with different name everyday, if it's same then it is perfect. You can grant respective privileges.

Regards,
Harsh

Re: Problem in creating database link [message #231912 is a reply to message #230106] Wed, 18 April 2007 17:39 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi ,

Sorry for replying back so lately.

Reloading user doesnt change the tablenames but how about the grants.They will be over written right ?It means that we need to grant them every day the user is reloaded.

moreover a nightly job is scheduled for this import process so it will be difficult to monitor such things right.

Can you give throw some light on this as well.

Thanks in Advance






Re: Problem in creating database link [message #232311 is a reply to message #231912] Fri, 20 April 2007 01:58 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi,

Are you reloading the user at each night?
Why do you need to do so? Just for the 'import' ?
If yes, then why don't you create the special user for that import procedure,if it is so important to you that you need to repeat the process of reloading them daily..

Exactly, what's your problem..?
Previous Topic: Listener fails to start
Next Topic: dead locks
Goto Forum:
  


Current Time: Thu Dec 08 20:24:50 CST 2016

Total time taken to generate the page: 0.18737 seconds