|
"As
a tale, so is life; not how long it is, but how good it is, is
what matters."
-Seneca
(4? B.C.-65 A.D.)
|
How
to create database link?
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;
|
