Monday, 27 December 2010

Creating a database link to access objects on a remote database

1. service name in tnsname
2. create a database link on the local database
3.connect using this database link to remote database

Service Name
To create a database link that points to a remote database you should have proper service name entry in local computer's tnsnames.ora file

Adding service name details in the tnsnames file to access remote database
(if this is already existing ignore it)

ex: tns entry to connect to a remote database running on testserver using service name 'test'

test =
(ADDRESS = (PROTOCOL = TCP)(HOST = testserver)(PORT = 1522))

create database link on the local database (the database from which you are trying to access another database)


create database link link_name connect to user_name identified by password using service_name

link_name = this is the name for the database link
username = username on the remote database. this username will be used to connect to remote             database
password = password for the user specified
service_name = this is the service name that is in the local database's tnsnames.ora file.


create database link 'testlink' connect to hr identified by hr using 'test'

Accessing objects on remote


select * from employees@testlink;

Saturday, 18 December 2010

XXX is not a valid responsibility for the current user. Please contact your System Administrator" – oracle apps 11i

This issue happens when you end date a responsibility and give a new responsibility to a user. Once user tries to log in “XXX is not a valid responsibility for the current user. Please contact your System Administrator”. This is because of cache at server, oracle does this to speed up the process.

Clearing this cache will make the user to log in with new responsibility.

Clearing the cache

go to > Functional Administrator responsibility
Click on Core services tab (the tab at the top right)
Click on Caching Framework Tab (blue tab under main tab)
Click on Global configuration
Click on Clear All Cache
A warning message related to performance will come , say yes

Screen shots

After selecting functional administrator responsibility