|
How to
create database link?
As
a DBA, you want to create Database links between two databases, databases
“A” and database “B”:
By:
John Kazerooni
As
a DBA, you want to create Database links between two databases, databases
“A” and database “B”:
Assuming
that all the users of database “A” want to read only from Scott’s tables.
You
need to do the following steps:
1-
Make sure to add a service name to each server.
For
example: (Add this to the tnsnames.ora file of server “B.”
A
=
(DESCRIPTION =
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = your-hostname)(PORT = your-port))
)
(CONNECT_DATA
=
(SERVICE_NAME
= A)
)
)
2-
Test your service name.
$tnsping
A
--OR--
MS-DOS>tnsping
A
3-
Now, the DBA of database “B” should create a username that can have
“READ ONLY” privilege to the Scott’s “EMP” tables.
CREATE
USER users2read IDENTIFIED BY pass;
GRANT
CREATE SESSION TO users2read;
GRANT
SELECT ON scott.emp TO users2read;
4-
Login to SQLPLUS and create a database link at server “A” to access
to users2read schema on the “B” database.
CREATE
PUBLIC DATABASE LINK read_scott_emp
CONNECT TO users2read
IDENTIFIED BY pass USING ‘B’;
5-
Create a public synonym for Scott’s table.
CREATE
PUBLIC SYNONYM emp FOR emp@read_scott_emp;
6-
Now, all the users on the database “A” can read only the Scott’s
EMP table from the “B” database.
SELECT * FROM emp;
|