A database link is the connection between two database servers.Database links are used to access the server side records from client side.It is possible if we created the database link.
The main advantage of the database links is that they allow users to access the another users objects in a remote database.
Demo:--
192.168.75.129 subbu1.erp.com(host name) server side
[oracle@subbu1 ~]$ . subbu1.env
[oracle@subbu1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:39:07 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@subbu1 ~]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:39:54
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start subbu1
Starting /u01/app/oracle/product/11.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(HOST=subbu1.erp.com)(PROTOCOL=TCP)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias subbu1
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 22-SEP-2016 14:40:03
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))
Services Summary...
Service "subbu1" has 1 instance(s).
Instance "subbu1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
192.168.75.132 subbu2.erp.com(host name) client side
[oracle1@subbu2 ~]$ . subbu2.env
[oracle1@subbu2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:41:55 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
[oracle1@subbu2 ~]$ tnsping to_subbu1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:45:42
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (HOST = subbu1.erp.com)(PROTOCOL = TCP)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = subbu1)))
OK (5380 msec)
server:--
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U1
A1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
9 rows selected.
SQL> grant connect,resource to l1 identified by l1;
Grant succeeded.
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U1
A1
L1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
10 rows selected.
SQL> conn l1/l1;
Connected.
SQL> sho user;
USER is "L1"
SQL> select * from tab;
no rows selected
SQL> @?/sqlplus/demo/demobld
BUILDING DEMONSTRATION TABLES.PLEASE WAIT
DEMONSTRATION TABLES BUILD IS COMPLETE
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
SQL> select count(*) from emp;
COUNT(*)
----------
14
client:---
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U2
DL
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
SQL> grant connect,resource to dl1 identified by dl1;
Grant succeeded.
Here we are granting create database link privilige to user dl1.if we want to check check what are the privileges for a user?
select * from session_privs;
SQL> grant create database link to dl1;
Grant succeeded.
SQL> conn dl1/dl1;
Connected.
SQL> select * from tab;
no rows selected
SQL> sho user;
USER is "DL1"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
11 rows selected.
SQL> select * from user_db_links;
no rows selected
here we are trying to create the database link for serverside user l1 and client side user dl1 and we are trying to connect to server side user l1 with the password l1 from client side user dl1.
SQL> create database link dl1l1 connect to l1 identified by l1 using 'to_subbu1';
Database link created.
SQL> select * from user_db_links;
DB_LINK USERNAME PASSWORD HOST
---------- ---------- ---------- ----------
CREATED
---------------
DL1L1 L1 to_subbu1
22-SEP-16
SQL> SHO USER;
USER is "DL1"
SQL> select * from tab;
no rows selected
SQL> select count(*) from emp@dl1l1;
COUNT(*)
----------
14
SQL> select * from tab@dl1l1;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
SQL> sho user;
USER is "DL1"
SQL> select * from tab;
no rows selected
The main advantage of the database links is that they allow users to access the another users objects in a remote database.
Demo:--
192.168.75.129 subbu1.erp.com(host name) server side
[oracle@subbu1 ~]$ . subbu1.env
[oracle@subbu1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:39:07 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@subbu1 ~]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:39:54
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start subbu1
Starting /u01/app/oracle/product/11.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(HOST=subbu1.erp.com)(PROTOCOL=TCP)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias subbu1
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 22-SEP-2016 14:40:03
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))
Services Summary...
Service "subbu1" has 1 instance(s).
Instance "subbu1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
192.168.75.132 subbu2.erp.com(host name) client side
[oracle1@subbu2 ~]$ . subbu2.env
[oracle1@subbu2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:41:55 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
[oracle1@subbu2 ~]$ tnsping to_subbu1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:45:42
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (HOST = subbu1.erp.com)(PROTOCOL = TCP)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = subbu1)))
OK (5380 msec)
server:--
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U1
A1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
9 rows selected.
SQL> grant connect,resource to l1 identified by l1;
Grant succeeded.
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U1
A1
L1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
10 rows selected.
SQL> conn l1/l1;
Connected.
SQL> sho user;
USER is "L1"
SQL> select * from tab;
no rows selected
SQL> @?/sqlplus/demo/demobld
BUILDING DEMONSTRATION TABLES.PLEASE WAIT
DEMONSTRATION TABLES BUILD IS COMPLETE
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
SQL> select count(*) from emp;
COUNT(*)
----------
14
client:---
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
U2
DL
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP
SQL> grant connect,resource to dl1 identified by dl1;
Grant succeeded.
Here we are granting create database link privilige to user dl1.if we want to check check what are the privileges for a user?
select * from session_privs;
SQL> grant create database link to dl1;
Grant succeeded.
SQL> conn dl1/dl1;
Connected.
SQL> select * from tab;
no rows selected
SQL> sho user;
USER is "DL1"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
11 rows selected.
SQL> select * from user_db_links;
no rows selected
here we are trying to create the database link for serverside user l1 and client side user dl1 and we are trying to connect to server side user l1 with the password l1 from client side user dl1.
SQL> create database link dl1l1 connect to l1 identified by l1 using 'to_subbu1';
Database link created.
SQL> select * from user_db_links;
DB_LINK USERNAME PASSWORD HOST
---------- ---------- ---------- ----------
CREATED
---------------
DL1L1 L1 to_subbu1
22-SEP-16
SQL> SHO USER;
USER is "DL1"
SQL> select * from tab;
no rows selected
SQL> select count(*) from emp@dl1l1;
COUNT(*)
----------
14
SQL> select * from tab@dl1l1;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
SQL> sho user;
USER is "DL1"
SQL> select * from tab;
no rows selected
No comments:
Post a Comment