Creating Location Transparency - Oracle 10g

After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. The following sections explain how to hide distributed functionality from users:

  • Using Views to Create Location Transparency
  • Using Synonyms to Create Location Transparency
  • Using Procedures to Create Location Transparency

Using Views to Create Location Transparency

Local views can provide location transparency for local and remote tables in a distributed database system.

For example, assume that table emp is stored in a local database and table dept is stored in a remote database. To make these tables transparent to users of the system, you can create a view in the local database that joins local and remote data:

Views and Location Transparency

Views and Location Transparency

When users access this view, they do not need to know where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example, the following query provides data from both the local and remote database table:

SELECT * FROM company;

The owner of the local view can grant only those object privileges on the local view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management for views that reference local data.

Using Synonyms to Create Location Transparency

Synonyms are useful in both distributed and nondistributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If you must rename or move the underlying object, you only need to redefine the synonym; applications based on the synonym continue to function normally. Synonyms also simplify SQL statements for users in a distributed database system.

Creating Synonyms

You can create synonyms for the following:

  • Tables
  • Types
  • Views
  • Materialized views
  • Sequences
  • Procedures
  • Functions
  • Packages

All synonyms are schema objects that are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow single- word access to remote data, hiding the specific object name and the location from users of the synonym.

The syntax to create a synonym is:


  • PUBLIC is a keyword specifying that this synonym is available to all users.

    Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.

  • synonym_name specifies the alternate object name to be referenced by users and applications.
  • schema specifies the schema of the object specified in object_name. Omitting this parameter uses the schema of the creator as the schema of the object.
  • object_name specifies either a table, view, sequence, materialized view, type, procedure, function or package as appropriate.
  • database_link_name specifies the database link identifying the remote database and schema in which the object specified in object_name is located.

A synonym must be a uniquely named object for its schema. If a schema contains a schema object and a public synonym exists with the same name, then the database always finds the schema object when the user that owns the schema references that name.

Example: Creating a Public Synonym

Assume that in every database in a distributed database system, a public synonym is defined for the scott.emp table stored in the hq database:


You can design an employee management application without regard to where the application is used because the location of the table is hidden by the public synonyms. SQL statements in the application access the table by referencing the public synonym emp.

Furthermore, if you move the emp table from the hq database to the hr database, then you only need to change the public synonyms on the nodes of the system. The employee management application continues to function properly on all nodes.

Managing Privileges and Synonyms

A synonym is a reference to an actual object. A user who has access to a synonym for a particular schema object must also have privileges on the underlying schema object itself. For example, if the user attempts to access a synonym but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

Assume scott creates local synonym emp as an alias for remote object scott cannot grant object privileges on the synonym to another local user. scott cannot grant local privileges for the synonym because this operation amounts to granting privileges for the remote emp table on the sales database, which is not allowed. This behavior is different from privilege management for synonyms that are aliases for local tables or views. Therefore, you cannot manage local privileges when synonyms are used for location transparency. Security for the base object is controlled entirely at the remote node. For example, user admin cannot grant object privileges for the emp _syn synonym.

Unlike a database link referenced in a view or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the schema in effect at the time the reference to the synonym is parsed. Therefore, to ensure the desired object resolution, it is especially important to specify the schema of the underlying object in the definition of a synonym.

Using Procedures to Create Location Transparency

PL/SQL program units called procedures can provide location transparency. You have these options:

  • Using Local Procedures to Reference Remote Data
  • Using Local Procedures to Call Remote Procedures
  • Using Local Synonyms to Reference Remote Procedures

Using Local Procedures to Reference Remote Data

Procedures or functions (either standalone or in packages) can contain SQL statements that reference remote data. For example, consider the procedure created by the following statement:

When a user or application calls the fire _emp procedure, it is not apparent that a remote table is being modified. A second layer of location transparency is possible when the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For example, the following statement defines a local synonym:


Given this synonym, you can create the fire_emp procedure using the following statement:

If you rename or move the table emp@hq, then you only need to modify the local synonym that references the table. None of the procedures and applications that call the procedure require modification.

Using Local Procedures to Call Remote Procedures

You can use a local procedure to call a remote procedure. The remote procedure can then execute the required DML. For example, assume that scott connects to local _db and creates the following procedure:

CONNECT scott/tiger@local _db

Now, assume that scott connects to the remote database and creates the remote procedure:

When a user or application connected to local_db calls the fire_emp procedure, this procedure in turn calls the remote term_emp procedure on

Using Local Synonyms to Reference Remote Procedures

For example, scott connects to the local database and creates the following procedure:

User peggy then connects to the database and creates the following synonym for the procedure that scott created on the remote sales database:

A local user on supply can use this synonym to execute the procedure on sales.

Managing Procedures and Privileges

Assume a local procedure includes a statement that references a remote table or view. The owner of the local procedure can grant the execute privilege to any user, thereby giving that user the ability to execute the procedure and, indirectly, access remote data.

In general, procedures aid in security. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics