 Database links explained... [message #163922] |
Mon, 20 March 2006 17:33  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Database links.
Okay, here is the deal. I just spent two days reading about something that a person can explain in two minutes.
1) Make sure your tnsnames.ora have the same correct entries on both local and remote databases.
then lsnrctl reload
The listener.ora may have some role in connecting?
2) create a public database link on the local database:
As sys (or a user that can create dblinks):
CREATE PUBLIC DATABASE LINK xxDBLINK
CONNECT TO LINKUSER
IDENTIFIED BY <a valid password>
USING '<a service name in your tnsnames.ora file that points to the remote database>';
3) On the remote database, create a user for the link to use:
CREATE USER LINKUSER IDENTIFIED BY <the valid password above>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE <an appropriate profile>
ACCOUNT UNLOCK;
4) GRANT CONNECT TO LINKUSER;
or perhaps
grant create session to linkuser;
5) grant object priveleges to select, and/or update, and/or delete, and/or insert for the remote table to the new linkuser:
grant select on scott.<a table>@xxDBLINK;
6) back on local database, create a public synonym:
create public synonym <remote table name> for <remote table owner>.<remote table name>@xxDBLINK;
Make sure local table names don't match table names on remote database. If so, then change the synonym name slightly.
7) Test it:
Then on your local database:
select count(*) from <tablename>
Unless I missed something. This works for Solaris 9 Oracle 10.2.0.1. to Linux Oracle 10.1.0.3. Watch out for firewalls or anything that would stop the two databases from seeing each other.
Happy linking!
Neil.
[Updated on: Mon, 20 March 2006 17:40] Report message to a moderator |
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
