A transaction is a logical unit of work constituted by one or more SQL statements executed by a single user. A transaction begins with the user's first executable SQL statement and ends when it is committed or rolled back by that user.
A remote transaction contains only statements that access a single remote node. A distributed transaction contain statements that access more than one node. The following sections define important concepts in transaction processing and explain how transactions access data in a distributed database:
Remote SQL Statements
A remote query statement is a query that selects information from one or more remote tables, all of which reside at the same remote node. For example, the following query accesses data from the dept table in the scott schema of the remote sales database:SELECT * FROM firstname.lastname@example.org_auto.com;
A remote update statement is an update that modifies data in one or more tables, all of which are located at the same remote node. For example, the following query updates the dept table in the scott schema of the remote sales database:
Distributed SQL Statements
A distributed query statement retrieves information from two or more nodes. For example, the following query accesses data from the local database as well as the remote sales database:
A distributed update statement modifies data on two or more nodes. A distributed update is possible using a PL/SQL subprogram unit such as a procedure or trigger that includes two or more remote updates that access data on different nodes. For example, the following PL/SQL program unit updates tables on the local database and the remote sales database:
The database sends statements in the program to the remote nodes, and their execution succeeds or fails as a unit.
Shared SQL for Remote and Distributed Statements
The mechanics of a remote or distributed statement using shared SQL are essentially the same as those of a local statement. The SQL text must match, and the referenced objects must match. If available, shared SQL areas can be used for the local and remote handling of any statement or decomposed query.
A remote transaction contains one or more remote statements, all of which reference a single remote node. For example, the following transaction contains two statements, each of which accesses the remote sales database:
A distributed transaction is a transaction that includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, this transaction updates the local database and the remote sales database:
Two-Phase Commit Mechanism
A database must guarantee that all statements in a transaction, distributed or nondistributed, either commit or roll back as a unit. The effects of an ongoing transaction should be invisible to all other transactions at all nodes; this transparency should be true for transactions that include any type of operation, including queries, updates, or remote procedure calls.
The general mechanisms of transaction control in a nondistributed database are discussed in the Oracle Database Concepts. In a distributed database, the database must coordinate transaction control with the same characteristics over a network and maintain data consistency, even if a network or system failure occurs.
The database two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. A two -phase commit mechanism also protects implicit DML operations performed by integrity constraints, remote procedure calls, and triggers.
Database Link Name Resolution
A global object name is an object specified using a database link. The essential components of a global object name are:
The following table shows the components of an explicitly specified global database object name:
Whenever a SQL statement includes a reference to a global object name, the database searches for a database link with a name that matches the database name specified in the global object name. For example, if you issue the following statement:SELECT * FROM email@example.com;
The database searches for a database link called orders.us.acme.com. The database performs this operation to determine the path to the specified remote database. The database always searches for matching database links in the following order:
Name Resolution When the Global Database Name Is Complete
Assume that you issue the following SQL statement, which specifies a complete global database name:SELECT * FROM firstname.lastname@example.org;
In this case, both the database name (prod1) and domain components (us.oracle.com) are specified, so the database searches for private, public, and global database links. The database searches only for links that match the specified global database name.
Name Resolution When the Global Database Name Is Partial
If any part of the domain is specified, the database assumes that a complete global database name is specified. If a SQL statement specifies a partial global database name (that is, only the database component is specified), the database appends the value in the DB _DOMAIN initialization parameter to the value in the DB _NAME initialization parameter to construct a complete name. For example, assume you issue the following statements:
If the network domain for locdb is us.acme.com, then the database appends this domain to orders to construct the complete global database name of orders.us.acme.com. The database searches for database links that match only the constructed global name. If a matching link is not found, the database returns an error and the SQL statement cannot execute.
Name Resolution When No Global Database Name Is Specified
If a global object name references an object in the local database and a database link name is not specified using the @ symbol, then the database automatically detects that the object is local and does not search for or use database links to resolve the object reference. For example, assume that you issue the following statements:
Because the second statement does not specify a global database name using a database link connect string, the database does not search for database links.
Terminating the Search for Name Resolution
The database does not necessarily stop searching for matching database links when it finds the first match. The database must search for matching private, public, and network database links until it determines a complete path to the remote database (both a remote account and service name). The first match determines the remote schema as illustrated in the following table:
User Operation Database Response Example table
After the database determines a complete path, it creates a remote session, assuming that an identical connection is not already open on behalf of the same local session. If a session already exists, the database reuses it.
Schema Object Name Resolution
After the local Oracle Database connects to the specified remote database on behalf of the local user that issued the SQL statement, object resolution continues as if the remote user had issued the associated SQL statement. The first match determines the remote schema according to the following rules:
Type of Link Specified Location of Object Resolution table If the database cannot find the object, then it checks public objects of the remote database. If it cannot resolve the object, then the established remote session remains but the SQL statement cannot execute and returns an error. The following are examples of global object name resolution in a distributed database system. For all the following examples, assume that:
Example of Global Object Name Resolution: Complete Object Name
This example illustrates how the database resolves a complete global object name and determines the appropriate path to the remote database using both a private and public database link. For this example, assume the following:
Consider the following statements issued by scott at the local database:
Later, JWARD connects and issues the following statements:
The database processes the final statement as follows:
Example of Global Object Name Resolution: Partial Object Name
This example illustrates how the database resolves a partial global object name and determines the appropriate path to the remote database using both a private and public database link.
For this example, assume that:
Complete Object Name"is already created on local database hq:
Consider the following statements issued at local database hq:
The database processes the final DELETE statement as follows:
Global Name Resolution in Views, Synonyms, and Procedures
A view, synonym, or PL/SQL program unit (for example, a procedure, function, or trigger) can reference a remote schema object by its global object name. If the global object name is complete, then the database stores the definition of the object without expanding the global object name. If the name is partial, however, the database expands the name using the domain of the local database name.
The following table explains when the database completes the expansion of a partial global object name for views, synonyms, and program units:
What Happens When Global Names Change
Global name changes can affect views, synonyms, and procedures that reference remote data using partial global object names. If the global name of the referenced database changes, views and procedures may try to reference a nonexistent or incorrect database. On the other hand, synonyms do not expand database link names at runtime, so they do not change.
Scenarios for Global Name Changes
Also, assume that the sales database contains the following view and synonym:
The database expands the employee synonym definition and stores it as:email@example.com
Scenario 1: Both Databases Change Names First, consider the situation where both the Sales and Human Resources departments are relocated to the United States. Consequently, the corresponding global database names are both changed as follows:
The following table describes query expansion before and after the change in global names:
Scenario 2: One Database Changes Names Now consider that only the Sales department is moved to the United States; Human Resources remains in the UK. Consequently, the corresponding global database names are both changed as follows:
The following table describes query expansion before and after the change in global names:
In this case, the defining query of the employee _names view expands to a nonexistent global database name. On the other hand, the employee synonym continues to reference the correct database, hq.uk.acme.com.
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.