OraFAQ Forum: Server Administration » DB link access to user

Home » RDBMS Server » Server Administration » DB link access to user (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DB link access to user [message #594791]

Tue, 03 September 2013 04:17

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

HI All,

I have login through the sysdba and created dblink and modified the tnsnames.ora for necessary changes.
And the it started working correctly and I can able to get the data by selecting it.But when I login with different
user that is showing the following error.

ORA-12154: TNS:could not resolve the connect identifier specified

How to resolve this,is there any privileges or permission need to give to that user.

Regards,
Nathan

Report message to a moderator

Re: DB link access to user [message #594792 is a reply to message #594791]

Tue, 03 September 2013 04:21

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Reads like the DB link is local.

Report message to a moderator

Re: DB link access to user [message #594793 is a reply to message #594792]

Tue, 03 September 2013 04:23

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

You shouldn't be creating anything as SYS either.

Report message to a moderator

Re: DB link access to user [message #594796 is a reply to message #594791]

Tue, 03 September 2013 04:43

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A databse link is private unless you create a PUBLIC database link.

As already pointed, you should create NOTHING in SYS schema.
You should not use SYS for this.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.

Regards
Michel

Report message to a moderator

Re: DB link access to user [message #594804 is a reply to message #594796]

Tue, 03 September 2013 05:08

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Thank you All,

Actually the issue is like with the same name two links has been created(one is private and other one is public).
while accessing the private one is taking defualt.After dropping the private link the issue got resolved.
And that db link created by using user only but no sys user.

Regards,
Nathan

Report message to a moderator

Re: DB link access to user [message #594863 is a reply to message #594804]

Tue, 03 September 2013 11:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

Report message to a moderator

Previous Topic:	number of connection for a user
Next Topic:	gathering database statistics

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 23:14:18 CST 2024