Home » RDBMS Server » Server Administration » DB link access to user (Oracle 10g)
DB link access to user [message #594791] Tue, 03 September 2013 04:17 Go to next message
sss111ind
Messages: 480
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
Re: DB link access to user [message #594792 is a reply to message #594791] Tue, 03 September 2013 04:21 Go to previous messageGo to next message
Roachcoach
Messages: 1198
Registered: May 2010
Location: UK
Senior Member
Reads like the DB link is local.
Re: DB link access to user [message #594793 is a reply to message #594792] Tue, 03 September 2013 04:23 Go to previous messageGo to next message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
You shouldn't be creating anything as SYS either.
Re: DB link access to user [message #594796 is a reply to message #594791] Tue, 03 September 2013 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: DB link access to user [message #594804 is a reply to message #594796] Tue, 03 September 2013 05:08 Go to previous messageGo to next message
sss111ind
Messages: 480
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
Re: DB link access to user [message #594863 is a reply to message #594804] Tue, 03 September 2013 11:11 Go to previous message
BlackSwan
Messages: 22718
Registered: January 2009
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/

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


Current Time: Sat Aug 30 09:47:35 CDT 2014

Total time taken to generate the page: 0.24199 seconds