End users do not necessarily know or care about the physical location of the databases that comprise the system.
Data is retrieved without any specific reference to the physical sites. Oracle Net8 accomplishes this through the use of transparent service names that hide the IP address, protocol, and remote database name.
In its most basic form, SQL*Net is a software tool that enables a network of Oracle clients and servers to communicate transparently on top of any underlying network topology or protocol via SQL.
Although SQL*Net is a very robust and sophisticated tool, you must appreciate the inherent complexity that accompanies its flexibility. This article provides a no-nonsense overview of the SQL*Net architecture.
All of the examples are based on Unix.
SQL*Net is not easy to install.
You need the following files on the server side in order to operate SQL*Net 2.0 on a Unix system:
- /etc/TNSNAMES.ORA : Used for outgoing database requests, this file contains all of the database names (or service identifiers, SIDs) running on the processor, as well as domain name, protocol, host, and port information.
When a new database is added to a box, you must update this file (changes to TNSNAMES.ORA become effective instantly). Note: SQL*Net version 1.0 equivalent is /etc/ORATAB.
- /etc/LISTENER.ORA : This file contains a list of destinations for outgoing database connections. When you add a new destination database to a Unix host, you must also add it to this file.
- /etc/HOSTS : This file lists all of your network addresses.
- /etc/SERVICES : This file lists all of the services available on a server, including SQL*Net services. It is no longer necessary with version 2.0.
- The only file required on the client side is /etc/TNSNAMES.ORA. (On a PC client, this file is determined by the path in the c:\windows\oracle.ini file.)
- (Although the Oracle Network Manager now sets up these file automatically, sometimes you must access or set them up manually.)
Location transparency, or location independence, means that neither applications nor users need to know the actual location of the
- views, or
- stored procedures
they are accessing. Oracle provides support for location transparency
by means of database links and synonyms.
Suppose that the fictitious Bigwheel Bicycle company wants to make its PRODUCTS table visible to its sales site, while the actual table resides at the headquarters site.
We can configure the sales sites so that a reference to PRODUCTS maps to the table in the headquarters site by creating a database link from PSLS.BIGWHEEL.COM to PHQS.BIGWHEEL.COM and creating a synonym for the remote object:
CREATE DATABASE LINK PHQS.BIGWHEEL.COM
CREATE SYNONYM products
FOR [email protected];