Lesson 2	Overview of distributed database enhancements
Objective	List primary Oracle enhancements for distributed databases.

Overview of Oracle Distributed Databases

The enhancements in Oracle9i for distributed systems focus on additional functionality to existing mechanisms.
These existing mechanisms include updateable snapshots, Advanced Queuing, data dictionary views, snapshot security, snapshot deployment, and replication of LOBs as detailed below. Updateable snapshots: Oracle has made significant enhancements to the baseline snapshot features that were first introduced in Oracle 7.3. One of the most exciting of these features is the updateable snapshot, whereby table information can be updated in remote sites and transferred back to the master table. Advanced Queuing: With the introduction of the DBMS_JOB package, Oracle has provided a very important tool called a queue. By using queues, multiple tasks can be serialized and executed in proper order, much like any input queue for online transactions. Oracle has enhanced the idea of queues within Oracle to allow the propagation of queue information to remote databases.

New data Dictionary Views

As the Oracle snapshots become more sophisticated, we see a new set of views that can be used to monitor the events taking place within the replicated snapshot environment.
Improved snapshot security: Snapshots security now can be applied to snapshots from the base tables.
Improved snapshot deployment templates: This is a new tool in Oracle that allows snapshot templates to be used to deploy snapshots to numerous remote databases.
Replication support for LOBs: You now can replicate all LOB columns except BFILE data types. The LOB data types are replacing LONG and LONG RAW data types. The older data types (LONG and LONG RAW) are not planned for replication support because they will be phased out of later versions of the database.
The next lesson looks at the queue propagation feature of Oracle.

Oracle Database Advanced Queuing in a Shared Server Environment

Queue operation scalability is similar to the underlying database operation scalability. If a dequeue operation with wait option is applied, then it does not return until it is successful or the wait period has expired. In a shared server environment, the shared server process is dedicated to the dequeue operation for the duration of the call, including the wait time. The presence of many such processes can cause severe performance and scalability problems and can result in deadlocking the shared server processes. For this reason, Oracle recommends that dequeue requests with wait option be applied using dedicated server processes. This restriction is not enforced.

DEQUEUE_OPTIONS_T Type

Specifies the options available for the dequeue operation.
Syntax

TYPE DEQUEUE_OPTIONS_T IS RECORD (
consumer_name VARCHAR2(30) DEFAULT NULL,
dequeue_mode BINARY_INTEGER DEFAULT REMOVE,
navigation BINARY_INTEGER DEFAULT NEXT_MESSAGE,
visibility BINARY_INTEGER DEFAULT ON_COMMIT,
wait BINARY_INTEGER DEFAULT FOREVER,
msgid RAW(16) DEFAULT NULL,
correlation VARCHAR2(128) DEFAULT NULL,
deq_condition VARCHAR2(4000) DEFAULT NULL,
signature aq$_sig_prop DEFAULT NULL,
transformation VARCHAR2(61) DEFAULT NULL,
delivery_mode PLS_INTEGER DEFAULT PERSISTENT);

Attributes

DEQUEUE_OPTIONS_T Attributes

consumer_name	Name of the consumer. Only those messages matching the consumer name are accessed. If a queue is not set up for multiple consumers, then this field should be set to NULL.For secure queues, consumer_name must be a valid AQ agent name, mapped to the database user performing the dequeue operation, through dbms_aqadm.enable_db_access procedure call.
dequeue_mode	Specifies the locking behavior associated with the dequeue. Possible settings are: BROWSE: Read the message without acquiring any lock on the message. This specification is equivalent to a select statement. LOCKED: Read and obtain a write lock on the message. The lock lasts for the duration of the transaction. This setting is equivalent to a select for update statement. REMOVE: Read the message and delete it. This setting is the default. The message can be retained in the queue table based on the retention properties. REMOVE_NODATA: Mark the message as updated or deleted. The message can be retained in the queue table based on the retention properties.
navigation	Specifies the position of the message that will be retrieved. First, the position is determined. Second, the search criterion is applied. Finally, the message is retrieved. Possible settings are: NEXT_MESSAGE: Retrieve the next message that is available and matches the search criteria. If the previous message belongs to a message group, then AQ retrieves the next available message that matches the search criteria and belongs to the message group. This setting is the default. NEXT_TRANSACTION: Skip the remainder of the current transaction group (if any) and retrieve the first message of the next transaction group. This setting can only be used if message grouping is enabled for the current queue. FIRST_MESSAGE: Retrieves the first message which is available and matches the search criteria. This setting resets the position to the beginning of the queue. FIRST_MESSAGE_MULTI_GROUP: indicates that a call to DBMS_AQ.DEQUEUE_ARRAY will reset the position to the beginning of the queue and dequeue messages (possibly across different transaction groups) that are available and match the search criteria, until reaching the ARRAY_SIZE limit. Refer to the TRANSACTION_GROUP attribute for the message to distinguish between transaction groups. NEXT_MESSAGE_MULTI_GROUP: indicates that a call to DBMS_AQ.DEQUEUE_ARRAY will dequeue the next set of messages (possibly across different transaction groups) that are available and match the search criteria, until reaching the ARRAY_SIZE limit. Refer to the TRANSACTION_GROUP attribute for the message to distinguish between transaction groups.
visibility	Specifies whether the new message is dequeued as part of the current transaction.The visibility parameter is ignored when using the BROWSE dequeue mode. Possible settings are: ON_COMMIT: The dequeue will be part of the current transaction. This setting is the default. IMMEDIATE: The dequeue operation is not part of the current transaction, but an autonomous transaction which commits at the end of the operation
wait	Specifies the wait time if there is currently no message available which matches the search criteria. Possible settings are: FOREVER: Wait forever. This setting is the default. NO_WAIT: Do not wait. number: Wait time in seconds.
msgid	Specifies the message identifier of the message to be dequeued.
correlation	Specifies the correlation identifier of the message to be dequeued. Special pattern matching characters, such as the percent sign (%) and the underscore (_) can be used. If more than one message satisfies the pattern, then the order of dequeuing is undetermined.
deq_condition	A conditional expression based on the message properties, the message data properties, and PL/SQL functions. A deq_condition is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the WHERE clause of a SQL query). Message properties include priority, corrid and other columns in the queue table To specify dequeue conditions on a message payload (object payload), use attributes of the object type in clauses. You must prefix each attribute with tab.user_data as a qualifier to indicate the specific column of the queue table that stores the payload. The deq_condition parameter cannot exceed 4000 characters. If more than one message satisfies the dequeue condition, then the order of dequeuing is undetermined.
signature	Currently not implemented
transformation	Specifies a transformation that will be applied after dequeuing the message. The source type of the transformation must match the type of the queue.
delivery_mode	The dequeuer specifies the delivery mode of the messages it wishes to dequeue in the dequeue options. It can be BUFFERED or PERSISTENT or PERSISTENT_OR_BUFFERED. The message properties of the dequeued message indicate the delivery mode of the dequeued message. Array dequeue is only supported for buffered messages with an array size of '1'.

Oracle Database Administration