Database Link in oracle (Server to Server)

·                     A  database link is schema object.

·                     A    database link is a  connection between  two physical database servers that allows a client to  access them as  one logical database.

WHY USE DATABASE LINK:-

·                     The most advantage  of database link is that they allow users to access another users objects  in a remote   database so that  they  are bounded by the privileges  set of the object owner.

·                     They are three basic types of database link.

TYPES:-

1.private

2.public

GRANT PRIVILEGES:-

Sql>grant create database link to username;

Sql>grant create public database link to username;

TYPES:-

1.private database link:-

·                     Create link in specific schema of the local database.

·                     May be used only by the link owner.

Syntax:-

Sql>create private database link   connect to identified by using ‘tnsname’;

Example:-

Sql>create  private database link xla1 connect to u1 identified by u1 using ‘tnsxla12’;

2.public database link:-

·                     A   public database link that can be used by any user.

·                     You can access remote objects using insert,update and  delete statement.

Syntax:-

Sql>Create public database link connect to identified by using ‘tnsname’;

Example:-

Sql>create public database link xla1 connect to u1 identified by u1 using ‘tnsla1’;

DATABASE LINK RESTRICTIONS:-

·                     Grant privileges on remote  objects.                    

·                     Do support describe operations –tables,views,functions,procedures,mviews,packages.

CLOSE DATABASE LINK:-

 Syntax:-

Sql>alter session close database link ;

Example:-

Sql>alter session close database link xla1  ;

DROPPING A DATABASE LINK:-

Sql>drop database link ;

Sql>drop public database link ;

VIEWS:-

All_db_links

Dba_db_links

User_db_links

The local  database user can view the object.

Sql>select *  from tab@;

Sql>select * from tab@xla1;

practical:-

Test11> select name, password

2  from sys.user$

3  where name='NIJAM';

NAME                           PASSWORD

------------------------------ ------------------------------

NIJAM                         66EE6D5F9AB42E0F

1 row selected.

Changed current NIJAM password to temporary "tmp_pwd" and granted "create database link" privilege to NIJAM.

Test1> alter user nijam identified by tmp_pwd;

User altered.

Test1> grant create database link to nijam;

Grant succeeded.

Connect as NIJAM user using temporary password and creat database link.

Test1> connect nijam/tmp_pwd@test11

Connected.

nijam@TEST11> CREATE DATABASE LINK U1_LINK

2   CONNECT TO U1

3   IDENTIFIED BY u1

4   USING '(DESCRIPTION =

5         (ADDRESS_LIST =

6           (ADDRESS = (PROTOCOL = TCP)(HOST = linux01)(PORT = 1521))

7         )

8         (CONNECT_DATA =

9           (SID = TEST10)

10         )

11       )';

Database link created.

Simple test:

nijam@TEST11> select count(*) from u1.u1_log@u1_link;

COUNT(*)

----------

355412

1 row selected.

LINK Link is functioning perfectly and now I can revoke "create database link privilege" and return password as it was before.

nijam@TEST11> conn Test11

Enter password:

Connected.

Test1> revoke create database link from nijam;

Revoke succeeded.

Test1> alter user nijam identified by values '66EE6D5F9AB42E0F';

User altered.

 The key thing is to have prepared scripts to do this actions as quick as possible because when you change current schema password to temporary other users or u1s will not be able to log in.