Accessing Remote data with Database Links in Oracle10g

A Database link is an object in one database that enables users to access objects on another database. It basically enables users to access the data in remote database for querying and updating. Users thus have the flexibility of considering a group of distributed databases as if they were a single integrated database.

The basic requirement for a database link is that Oracle Net should be running on each of the machines hosts which are involved in the remote database access. Oracle net is usually started up by the Database Administrator.

While creating database links, care should be taken to provide the connection information such as the communication protocols to be used, the host in which the remote database resides, the name of the database on the remote host, the name of an account in the remote database and the password for that account.

The Database link can be private and owned by a single user or public and accessible to all users in the local database. A global Database link which is same as the global database name is registered by default in Oracle Names Server and can be used by any client in the network to access an object in the database.

When a public or private database link is created, in order to determine the schema on the remote database that the link will be connected to, it is necessary to specify if the database link is fixed user, connected user or current user. For a fixed user database link the credentials like user name and password required to access the remote database is embedded in the definition. For a connected user and current user database link the credentials are not embedded and may change corresponding to the user referencing the database link and the operation performed.

If many users access an application that references a remote object by a database link then there will be a high number of connections between local and remote database resulting in an increased workload in the server processes. An alternative approach is to use a shared database link when the number of users is more than the number of server processes in the local database.

A user in the local database can access objects in the remote database by appending the database link name to the name of the object that is in the remote database as shown below.

Example: select * from emp@remote_link

Accessing objects this way results in the same results as logging in directly to the remote database and executing the query directly.

The maximum number of database links that can be used in a single query has a default value of four. This can be changed via the open_links parameter which is set in the database initialization parameter file.

A local synonym or view that references remote objects can also be used for creating location transparency. For example in a local database it is created as,
create synonym emp_syn for empl_details @remote_link;

Every time the synonym is used in a query the remote database will be correspondingly queried. Through the use of synonyms the exact physical location of the database object does not appear to the user and it helps simplify application maintenance. Database link can also be used for remote updates with syntax to be used similar to that used for remote queries.

The SQL*Plus copy command allows data to be copied from one database to another. It has the benefit of allowing commit after each array of data has been processed. This reduces the burden on the rollback segment that is caused by large transactions. In general it was designed to be used for copying data between Oracle and non-Oracle databases.

It is also possible to connect to a remote database by an Oracle tool like SQL*Plus. To do this it is required to enter the username and password along with the Oracle net connect string for the remote database. Also the remote database has to be properly configured to listen to new connection requests and the corresponding client machine to issue connection requests. If this is done, Oracle Net can access the remote database without issues regardless of the configuration.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |