Manage database links

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

32.1 Managing Global Names in a Distributed System
32.2 Creating Database Links
32.2.3 Specifying Link Users
32.2.4 Using Connection Qualifiers to Specify Service Names Within Link Names
32.3 Using Shared Database Links
32.4 Managing Database Links
32.5 Viewing Information About Database Links
32.6 Creating Location Transparency
32.7 Managing Statement Transparency
32.8 Managing a Distributed Database: Examples
32.8.1 Example 1: Creating a Public Fixed User Database Link
32.8.2 Example 2: Creating a Public Fixed User Shared Database Link
32.8.3 Example 3: Creating a Public Connected User Database Link
32.8.4 Example 4: Creating a Public Connected User Shared Database Link
32.8.5 Example 5: Creating a Public Current User Database Link
Basic Usage for Database Link
DRIVING_SITE Hint

Managing a distributed database includes tasks such as managing 
global names, managing database links, and creating location and 
statement transparency.


32.1 Managing Global Names in a Distributed System
In a distributed database system, each database should have a unique 
global database name. Global database names uniquely identify a database 
in the system.

32.1.1 Understanding How Global Database Names Are Formed
A global database name is formed from two components: 
a database name and a domain.

DB_NAME.DB_DOMAIN

e.g.
sales.example.com
sales.us.example.com
mktg.us.example.com
payroll.example.org

32.1.2 Determining Whether Global Naming Is Enforced

If the local database enforces global naming, then you must use the 
remote database global database name as the name of the link.

-> To determine whether global naming is enforced on a database

SQL> COL NAME FORMAT A12
COL VALUE FORMAT A6
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names';

NAME VALUE
------------ ------
global_names FALSE

32.1.3 Viewing a Global Database Name

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-------------------------
CDB1

32.1.4 Changing the Domain in a Global Database Name

ALTER DATABASE RENAME GLOBAL_NAME TO database.domain;

#RENAME GLOBAL_NAME

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string cdb1

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain';

NAME         VALUE
------------ ------
db_domain

SQL> ALTER SYSTEM SET global_names=TRUE;

System altered.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO cdb1.example.com;

Database altered.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.EXAMPLE.COM


#Configure the DB Domain

SQL> ALTER SYSTEM SET db_domain ='EXAMPLE.COM' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET global_names = TRUE ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 666894440 bytes
Database Buffers 398458880 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string EXAMPLE.COM
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL>
SQL> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string cdb1.EXAMPLE.COM
SQL>
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.EXAMPLE.COM

-bash-4.1$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2019 11:46:58
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2.localdomain)(PORT=1521)))
Services Summary...
Service "cdb1.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdb1XDB.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 5656>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=62246))
Service "pdb1.example.com" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

32.1.5 Changing a Global Database Name: Scenario

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.EXAMPLE.COM

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain';

NAME VALUE
--------------------------------------------------------------------------------
db_domain EXAMPLE.COM

SQL> CREATE DATABASE LINK hq USING 'cdb1';

Database link created.

SQL> SELECT DB_LINK FROM USER_DB_LINKS;

DB_LINK
--------------------------------------------------------------------------------
HQ.EXAMPLE.COM

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO cdb1.jp.example.com;

Database altered.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.JP.EXAMPLE.COM

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain';

NAME VALUE
--------------------------------------------------------------------------------
db_domain EXAMPLE.COM


SQL> CREATE DATABASE LINK supply USING 'cdb1';

Database link created.

SQL> SELECT DB_LINK FROM USER_DB_LINKS;

DB_LINK
--------------------------------------------------------------------------------
HQ.EXAMPLE.COM
SUPPLY.JP.EXAMPLE.COM

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO cdb1.asia.jp.example.com;

Database altered.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.ASIA.JP.EXAMPLE.COM

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain';

NAME VALUE
--------------------------------------------------------------------------------
db_domain EXAMPLE.COM


SQL> CREATE DATABASE LINK warehouse USING 'cdb1';

Database link created.

SQL> SELECT DB_LINK FROM USER_DB_LINKS;

DB_LINK
--------------------------------------------------------------------------------
HQ.EXAMPLE.COM
SUPPLY.JP.EXAMPLE.COM
WAREHOUSE.ASIA.JP.EXAMPLE.COM

SQL> drop database link HQ.EXAMPLE.COM;

Database link dropped.

SQL> drop database link SUPPLY.JP.EXAMPLE.COM
2 /

Database link dropped.

SQL> drop database link WAREHOUSE.ASIA.JP.EXAMPLE.COM;

Database link dropped.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
CDB1.ASIA.JP.EXAMPLE.COM

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO EXAMPLE.COM;

Database altered.

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
EXAMPLE.COM

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME IN ('db_domain','global_names');

NAME VALUE
--------------------------------------------------------------------------------
db_domain EXAMPLE.COM
global_names TRUE


32.2 Creating Database Links
-> To support application access to the data and schema objects throughout 
a distributed database system, you must create all necessary database links.

32.2.1 Obtaining Privileges Necessary for Creating Database Links

Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. 
To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. 
Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Oracle Net must be installed on both the local and remote Oracle databases.


SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
'CREATE PUBLIC DATABASE LINK')
/

32.2.2 Specifying Link Types
When you create a database link, you must decide who will have access to it.

32.2.2.1 Creating Private Database Links
Use the CREATE DATABASE LINK statement to create private database links.

-> To create a private database link, specify the following 
(where link_name is the global database name or an arbitrary link name):
CREATE DATABASE LINK link_name ...;

32.2.2.2 Creating Public Database Links
Use the CREATE PUBLIC DATABASE LINK statement to create public database links.

-> To create a public database link, use the keyword PUBLIC 
(where link_name is the global database name or an arbitrary link name):
CREATE PUBLIC DATABASE LINK link_name ...;

32.2.2.3 Creating Global Database Links

Global database link – defined in an OID or Oracle Names Server. 
Anyone on the network can use it.

32.2.3 Specifying Link Users

A database link defines a communication path from one database to another. 
When an application uses a database link to access a remote database, 
Oracle Database establishes a database session in the remote database on 
behalf of the local application request. When you create a private or public 
database link, you can determine which schema on the remote database the link 
will establish connections to by creating fixed user, current user, and connected 
user database links.

32.2.3.1 Creating Fixed User Database Links

CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;

32.2.3.2 Creating Connected User and Current User Database Links

Note:
[1] Connected user and current user database links do not include credentials 
    in the definition of the link.
[2] The credentials used to connect to the remote database can change 
    depending on the user that references the database link and the operation 
    performed by the application.

32.2.3.2.1 Creating a Connected User Database Link
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];
CREATE DATABASE LINK sales.division3.example.com USING 'sales';

32.2.3.2.2 Creating a Current User Database Link
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER [USING 'net_service_name'];
CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';

32.2.4 Using Connection Qualifiers to Specify Service Names Within Link Names

To use a service name to specify a particular instance, include the service name 
at the end of the global object name:
SELECT * FROM scott.emp@hq.example.com@hq_1
Note that in this example, there are two @ symbols.

32.3 Using Shared Database Links

-> Every application that references a remote server using a standard database 
   link establishes a connection between the local database and the remote database. 
   Many users running applications simultaneously can cause a high number of 
   connections between the local and remote databases. 
-> Shared database links enable you to limit the number of network 
   connections required between the local server and the remote server.

32.3.1 Determining Whether to Use Shared Database Links

Look carefully at your application and shared server configuration to 
determine whether to use shared links. A simple guideline is to use 
shared database links when the number of users accessing a database link 
is expected to be much larger than the number of server processes 
in the local database.

Shared database links are not useful in all situations. 
Assume that only one user accesses the remote server. If this user defines 
a shared database link and 10 shared server processes exist in the local 
database, then this user can require up to 10 network connections to the 
remote server. Because the user can use each shared server process, 
each process can establish a connection to the remote server.

Clearly, a nonshared database link is preferable in this situation because 
it requires only one network connection. Shared database links lead to 
more network connections in single-user scenarios so use shared links only 
when many users need to use the same link. Typically, shared links are used 
for public database links, but can also be used for private database 
links when many clients access the same local schema

32.3.2 Creating Shared Database Links
To create a shared database link, use the keyword SHARED in the CREATE DATABASE LINK statement.

CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];

Whenever you use the keyword SHARED, the clause AUTHENTICATED BY is required.

CREATE SHARED DATABASE LINK link2sales
CONNECT TO scott IDENTIFIED BY password
AUTHENTICATED BY linkuser IDENTIFIED BY ostrich
USING 'sales';

32.3.3 Configuring Shared Database Links
You can configure shared database links in different ways.

32.3.3.1 Creating Shared Links to Dedicated Servers

A shared server process in the local server can own a dedicated remote 
server process. The advantage is that a direct network transport exists 
between the local shared server and the remote dedicated server. 
A disadvantage is that extra back-end server processes are needed.

Figure 32-1 A Shared Database Link to Dedicated Server Processes
shows a shared database link to dedicated server processes. 
A client workstation owns several user processes which link to a local server 
through dispatcher processes. A shared server process on the local server owns a 
link to a dedicated remote server process. The shared server process on the 
local server interacts with the local system global area (SGA). The dedicated 
server processes on the remote server interact with the remote SGA.


32.3.3.2 Creating Shared Links to Shared Servers
You can create shared links using shared server processes on the remote server.

This configuration eliminates the need for more dedicated servers, but requires 
the connection to go through the dispatcher on the remote server. Note that both 
the local and the remote server must be configured as shared servers.

Figure 32-2 Shared Database Link to Shared Server
This shows a shared database link to a shared server. The shared server processes, 
in this configuration, are located on the remote server. User processes on the 
client connect to shared server processes on the server through dispatcher 
processes on the remote server.

32.4 Managing Database Links

Managing database links includes tasks such as closing them, dropping them, 
and limiting the number of active connections to them.

32.4.1 Closing Database Links
If you access a database link in a session, then the link remains open until 
you close the session. To close a database link manually, use the 
ALTER SESSION CLOSE DATABASE LINK statement.

A link is open in the sense that a process is active on each of the remote 
databases accessed through the link. 
This situation has the following consequences:
If 20 users open sessions and access the same public link in a local database, 
then 20 database link connections are open.

If 20 users open sessions and each user accesses a private link, 
then 20 database link connections are open.

If one user starts a session and accesses 20 different links, 
then 20 database link connections are open.

After you close a session, the links that were active in the session are 
automatically closed. You may have occasion to close the link manually. 
For example, close links when:

The network connection established by a link is used infrequently in an application.
The user session must be terminated.

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session.

32.4.2 Dropping Database Links

You can drop a database link just as you can drop a table or view. 
If the link is private, then it must be in your schema.
If the link is public, then you must have the DROP PUBLIC DATABASE LINK 
system privilege.

DROP [PUBLIC] DATABASE LINK dblink;

32.4.2.1 Dropping a Private Database Link
CONNECT scott@local_db

SELECT DB_LINK FROM USER_DB_LINKS;

DROP DATABASE LINK sales.us.example.com;


32.4.2.2 Dropping a Public Database Link
CONNECT SYSTEM@local_db AS SYSDBA

SELECT DB_LINK FROM DBA_DB_LINKS
WHERE OWNER = 'PUBLIC';

DROP PUBLIC DATABASE LINK sales.us.example.com;

32.4.3 Limiting the Number of Active Database Link Connections

You can limit the number of connections from a user process to remote databases 
using the static initialization parameter OPEN_LINKS.

SQL> show parameter OPEN_LINKS

NAME TYPE VALUE
-------------------------------------------------------------------
open_links integer 4
open_links_per_instance integer 4

This parameter controls the number of remote connections that a single 
user session can use concurrently in distributed transactions.

Note the following considerations for setting this parameter:

->The value should be greater than or equal to the number of databases 
  referred to in a single SQL statement that references multiple databases.
->Increase the value if several distributed databases are accessed over time. 
  Thus, if you regularly access three databases, set OPEN_LINKS to 3 or greater.
->The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, 
  then no distributed transactions are allowed.

32.5 Viewing Information About Database Links

32.5.1 Determining Which Links Are in the Database
COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

32.5.2 Determining Which Link Connections Are Open
COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

32.6 Creating Location Transparency
32.6.1 Using Views to Create Location Transparency
32.6.2 Using Synonyms to Create Location Transparency
32.6.3 Using Procedures to Create Location Transparency
32.7 Managing Statement Transparency

32.8 Managing a Distributed Database: Examples

Preparing database : orcl (12c NON CDB)

SQL> select name,value from v$parameter where name in ('db_domain','global_names');

NAME VALUE
--------------------------------------------------------------------------------
db_domain
global_names FALSE

SQL> ALTER SYSTEM SET GLOBAL_NAMES=TRUE;

System altered.

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO oraclelearn.com ;

Database altered.

SQL> ALTER SYSTEM SET db_domain='oraclelearn.com' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 692060264 bytes
Database Buffers 373293056 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORACLELEARN.COM

SQL> show parameter db_domain

NAME TYPE VALUE
---------------------------------------------------------------------
db_domain string oraclelearn.com

SQL> show parameter service

NAME TYPE VALUE
----------------------------------------------------------------------
service_names string orcl.oraclelearn.com
SQL>
SQL> quit

-bash-4.1$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2019 15:03:12
Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2.localdomain)(PORT=1521)))
Services Summary...
Service "cdb1.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
LOCAL SERVER
Service "cdb1XDB.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 5656>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=62246))
Service "orcl.oraclelearn.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:11 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.oraclelearn.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 14233>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=21100))
Service "pdb1.example.com" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
LOCAL SERVER
The command completed successfully
-bash-4.1$


-bash-4.1$ tnsping cdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2019 15:05:46
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1.example.com)))
OK (0 msec)
-bash-4.1$
-bash-4.1$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2019 15:05:50
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.example.com)))
OK (0 msec)

-------------------------------------------------------------
32.8.1 Example 1: Creating a Public Fixed User Database Link
-------------------------------------------------------------
SQL> connect sys/oracle_4U@racnode2/pdb1.example.com as sysdba
Connected.

SQL> create public database link orcl.oraclelearn.com
2 connect to scott identified by tiger using 'orcl';

Database link created.

SQL> select * from dual@orcl.oraclelearn.com;
select * from dual@orcl.oraclelearn.com
*
ERROR at line 1:
ORA-02085: database link ORCL.ORACLELEARN.COM connects to ORACLELEARN.COM

SQL> !oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
// The connection is rejected.
// *Action: create a database link with the same name as the database it
// connects to, or set global_names=false.
//

SQL> drop public database link ORCL.ORACLELEARN.COM;

Database link dropped.

SQL> create public database link ORACLELEARN.COM
2 connect to scott identified by tiger using 'orcl';

Database link created.

SQL> select * from dual@ORACLELEARN.COM;

DUM
---
X

SQL> connect t10/t10@pdb1
Connected.
SQL>
SQL> select * from dual@ORACLELEARN.COM;

DUM
---
X

SQL> select * from tab@ORACLELEARN.COM;

TNAME TABTYPE CLUSTERID
------------------------------------------------------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
FBVQ TABLE
SALGRADE TABLE
TBL_FBVQ TABLE
TBL_TEST_FB_VQ TABLE

7 rows selected.

---------------------------------------------------------------------
32.8.2 Example 2: Creating a Public Fixed User Shared Database Link
---------------------------------------------------------------------

export ORACLE_SIDA=cdb1

SQL> alter system set shared_servers=3;
SQL> alter system set dispatchers='(INDEX=1)(PROTOCOL=TCP)(SERVICE=pdb1.example.com)';

-bash-4.1$ export ORACLE_SID=orcl
-bash-4.1$ sql

SQL> alter system set shared_servers=3;

SQL> alter system set dispatchers='(INDEX=1)(PROTOCOL=TCP)(SERVICE=orcl.oraclelearn.com)';

-bash-4.1$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-AUG-2019 15:37:12

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2.localdomain)(PORT=1521)))
Services Summary...
Service "cdb1.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:15 refused:0 state:ready
LOCAL SERVER
Service "cdb1XDB.EXAMPLE.COM" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 5656>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=62246))
Service "orcl.oraclelearn.com" has 1 instance(s).
Instance "orcl", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:94 refused:0 state:ready
LOCAL SERVER
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 16095>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=52989))
Service "orclXDB.oraclelearn.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 14233>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=21100))
Service "pdb1.example.com" has 1 instance(s).
Instance "cdb1", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:15 refused:0 state:ready
LOCAL SERVER
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: racnode2, pid: 15964>
(ADDRESS=(PROTOCOL=tcp)(HOST=racnode2.localdomain)(PORT=25276))
The command completed successfully

vi tnsnames.oral 
ORCL_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=SHARED)
(SERVICE_NAME = orcl.oraclelearn.com)
)
)


pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.example.com)
)
)

pdb1_s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = pdb1.example.com)
)
)

-bash-4.1$ sqlplus sys/oracle_4U@pdb1_s as sysdba

SQL> drop public database link ORACLELEARN.COM;

Database link dropped.

SQL> CREATE SHARED PUBLIC DATABASE LINK ORACLELEARN.COM
connect to scott identified by tiger
authenticated by scott identified by tiger
using 'ORCL_S';

Database link created.

SQL> select * from dual@ORACLELEARN.COM;

DUM
---
X

SQL> select count(*) from emp@ORACLELEARN.COM;

COUNT(*)
----------
14

SQL> connect t10/t10@pdb1_s
Connected.
SQL>
SQL> select count(*) from emp@ORACLELEARN.COM;

COUNT(*)
----------
14


32.8.3 Example 3: Creating a Public Connected User Database Link

-bash-4.1$ sqlplus scott/tiger@pdb1
SQL> quit

-bash-4.1$ sqlplus scott/tiger@orcl
SQL> quit

-bash-4.1$ sqlplus sys/oracle_4U@pdb1 as sysdba
SQL> grant create database link,create public database link to scott;

Grant succeeded.

SQL> quit

-bash-4.1$ sqlplus scott/tiger@pdb1

SQL> create public database link ORACLELEARN.COM
2 USING 'ORCL';

Database link created.

SQL> select * from dept@ORACLELEARN.COM;

DEPTNO DNAME              LOC
---------------------------------------
10 ACCOUNTING   NEW YORK
20 RESEARCH     DALLAS
30 SALES        CHICAGO
40 OPERATIONS   BOSTON

SQL> select * from dual@ORACLELEARN.COM;

DUM
---
X

SQL> connect system/oracle_4U@pdb1
Connected.
SQL> select * from dual@ORACLELEARN.COM;

DUM
---
X

SQL> select count(*) from tab@ORACLELEARN.COM;

COUNT(*)
----------
200


32.8.4 Example 4: Creating a Public Connected User Shared Database Link

-bash-4.1$ sqlplus sys/oracle_4U@pdb1 as sysdba

SQL> drop public database link ORACLELEARN.COM;

Database link dropped.

-bash-4.1$ sqlplus scott/tiger@pdb1_s

SQL> CREATE SHARED PUBLIC DATABASE LINK ORACLELEARN.COM
2 AUTHENTICATED BY scott IDENTIFIED BY tiger
3 USING 'ORCL_S';

Database link created.

SQL> select count(*) from tab@ORACLELEARN.COM;

COUNT(*)
----------
7

SQL> connect system/oracle_4U@pdb1_s
Connected.
SQL>
SQL> select count(*) from tab@ORACLELEARN.COM;

COUNT(*)
----------
200

32.8.5 Example 5: Creating a Public Current User Database Link

CREATE PUBLIC DATABASE LINK sales.division3.example.com
CONNECT TO CURRENT_USER
USING 'sldb';

Note:
To use this link, the current user must be a global user.
A global user in a CURRENT_USER database link. The global user must be 
authenticated by an X.509 certificate (an SSL-authenticated enterprise user) 
or a password (a password-authenticated enterprise user), and be a user on 
both databases involved in the link.

------------
Basic Usage for Database Link
------------
sqlplus scott/tiger@orcl
SQL> create or replace procedure delete_emp(p_empno NUMBER)
as
BEGIN
DELETE EMP
WHERE EMPNO=p_empno;
END;
/
Procedure created.

sqlplus scott/tiger@pdb1
SQL> create database link ORACLELEARN.COM
2 connect to scott identified by tiger
3 using 'ORCL';

Database link created.

SQL> select * from emp@ORACLELEARN.COM;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------- ---------- ---------- --------------------------------------------------------------------------------------------------
7369 SMITH CLERK 7902 Dec 17 1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 Feb 20 1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 Feb 22 1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 Apr 02 1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 Sep 28 1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 May 01 1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 Jun 09 1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 Apr 19 1987 00:00:00 3000 20
7839 KING PRESIDENT Nov 17 1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 Sep 08 1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 May 23 1987 00:00:00 1100 20
7900 JAMES CLERK 7698 Dec 03 1981 00:00:00 950 30
7902 FORD ANALYST 7566 Dec 03 1981 00:00:00 3000 20
7934 MILLER CLERK 7782 Jan 23 1982 00:00:00 1300 10


14 rows selected.

SQL> INSERT INTO emp@ORACLELEARN.COM
2 VALUES ( 9999,'hitesh','dba',1111,sysdate,99999,0,10);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE emp@ORACLELEARN.COM
2 SET ENAME='HG'
3 WHERE EMPNO=9999;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM emp@ORACLELEARN.COM
2 WHERE EMPNO=9999;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> EXEC delete_emp@ORACLELEARN.COM(7934);

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM EMP;

COUNT(*)
----------
14

SQL> SELECT COUNT(*) FROM EMP@ORACLELEARN.COM;

COUNT(*)
----------
13

-----------
Performance : DRIVING_SITE Hint
-----------
For example:

SELECT /*+ DRIVING_SITE(departments) */ * 
FROM employees, departments@rsite 
WHERE employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from departments are 
sent to the local site, and the join is executed there. With the hint, 
the rows from employees are sent to the remote site, and the query is executed 
there and the result set is returned to the local site.

----------------------
Path to Documentation:
----------------------
Database Administrator’s Guide
31 Distributed Database Concepts
32 Managing a Distributed Database

Thank you for visiting this blog…