Managing Streams Queues

A Streams queue stages events whose payloads are of SYS.AnyData type. Therefore, a Streams queue can stage an event with payload of nearly any type, if the payload is wrapped in a SYS.AnyData wrapper. Each Streams capture process and apply process is associated with one Streams queue, and each Streams propagation job is associated with one Streams source queue and one Streams destination queue.

This section provides instructions for completing the following tasks related to Streams queues:

Creating a Streams Queue

You use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to create a Streams queue. This procedure enables you to specify the following for the Streams queue it creates:

The queue table for the queue

A storage clause for the queue table

The queue name

A queue user that will be configured as a secure queue user of the queue and granted ENQUEUE and DEQUEUE privileges on the queue

A comment for the queue

This procedure creates a queue that is both a secure queue and a transactional queue and starts the newly created queue.

For example, to create a Streams queue named strm01_queue with a queue table named strm01_queue_table and grant the hr user the privileges necessary to enqueue events into and dequeue events from the queue, run the following procedure:

Enabling a User to Perform Operations on a Secure Queue

For a user to perform queue operations, such as enqueue and dequeue, on a secure queue, the user must be configured as a secure queue user of the queue. If you use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to create the secure queue, then the queue owner and the user specified by the queue_user parameter are configured as secure users of the queue automatically. If you want to enable other users to perform operations on the queue, then you can configure these users in one of the following ways:

Run SET_UP_QUEUE and specify a queue_user. Queue creation is skipped if the queue already exists, but a new queue user is configured if one is specified.

Associate the users with an agent manually

The following example illustrates associating a user with an agent manually. Suppose you want to enable the oe user to perform queue operations on the strm01_queue created in "Creating a Streams Queue". The following steps configure the oe user as a secure queue user of strm01_queue:

Connect as an administrative user who can create agents and alter users.

Create an agent:

EXEC DBMS_AQADM.CREATE_AQ_AGENT(agent_name => 'strm01_queue_agent');

If the user must be able to dequeue events from queue, then make the agent a subscriber of the secure queue:

Grant the user EXECUTE privilege on the DBMS_AQ package, if the user is not already granted this privilege.

GRANT EXECUTE ON DBMS_AQ TO oe;

When these steps are complete, the oe user is a secure user of the strm01_queue queue and can perform operations on the queue. You still must grant the user specific privileges to perform queue operations, such as enqueue and dequeue privileges.

Disabling a User from Performing Operations on a Secure Queue

You may want to disable a user from performing queue operations on a secure queue for the following reasons:

You dropped a capture process, but you did not drop the queue that was used by the capture process, and you do not want the user who was the capture user to be able to perform operations on the remaining secure queue.

You dropped an apply process, but you did not drop the queue that was used by the apply process, and you do not want the user who was the apply user to be able to perform operations on the remaining secure queue.

You used the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to change the apply_user for an apply process, and you do not want the old apply_user to be able to perform operations on the apply process queue.

To disable a secure queue user, you can revoke ENQUEUE and DEQUEUE privilege on the queue from the user, or you can run the DISABLE_DB_ACCESS procedure in the DBMS_AQADM package. For example, suppose you want to disable the oe user from performing queue operations on the strm01_queue created in "Creating a Streams Queue".

Attention:

If an agent is used for multiple secure queues, then running DISABLE_DB_ACCESS for the agent prevents the user from performing operations on all of these queues.

Run the following procedure to disable the oe user from performing queue operations on the secure queue strm01_queue:

Each of the procedures in the DBMS_STREAMS_ADM package creates a propagation job with the specified name if it does not already exist, creates a rule set for the propagation job if the propagation job does not have a rule set, and may add table, schema, or global rules to the rule set. The CREATE_PROPAGATION procedure creates a propagation job, but does not create a rule set or rules for the propagation job. All propagation jobs are started automatically upon creation.

The following tasks must be completed before you create a propagation job:

Create a source queue and a destination queue for the propagation job, if they do not exist. See "Creating a Streams Queue" for instructions.

Creates a propagation job named strm01_propagation. The propagation job is created only if it does not already exist.

Specifies that the propagation job propagates LCRs from strm01_queue in the current database to strm02_queue in the dbs2.net database

Specifies that the propagation job uses the dbs2.net database link to propagate the LCRs, because the destination_queue_name parameter contains @dbs2.net

Creates a rule set and associates it with the propagation job, if the propagation job does not have a rule set. The rule set uses the evaluation context SYS.STREAMS$_EVALUATION_CONTEXT. The rule set name is specified by the system.

Creates two rules. One rule specifies that the propagation job propagates row LCRs that contain the results of DML changes to the hr.departments table, and the other rule specifies that the propagation job propagates DDL LCRs that contain changes to the hr.departments table. The rule names are specified by the system.

Adds the two rules to the rule set associated with the propagation job

Specifies that the propagation job propagates an LCR only if it has a NULL tag, because the include_tagged_lcr parameter is set to false. This behavior is accomplished through the system-created rules for the propagation job.

Specifies that the source database of the LCRs to be propagated is dbs1.net, which may or may not be the current database

Creates a propagation job named strm02_propagation. A propagation job with the same name must not exist.

Specifies that the propagation job propagates events from strm01_queue in the current database to strm02_queue in the dbs2.net database. Depending on the rules in the rule set, the propagated events may be captured events or user-enqueued events.

Specifies that the propagation job uses the dbs2.net database link to propagate the events

Associates the propagation job with an existing rule set named strm01_rule_set

Scheduling a Propagation Job

You can schedule a propagation job using the SCHEDULE_PROPAGATION procedure in the DBMS_AQADM package. If there is a problem with a propagation job, then unscheduling and scheduling the propagation job may correct the problem.

For example, the following procedure schedules a propagation job that propagates events from the strmadmin.strm01_queue source queue using the dbs2.net database link:

Altering the Schedule of a Propagation Job

You can alter the schedule of an existing propagation job using the ALTER_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package.

For example, suppose you want to alter the schedule of a propagation job that propagates events from the strmadmin.strm01_queue source queue using the dbs2.net database link. The following procedure sets the propagation job to propagate events every 15 minutes (900 seconds), with each propagation lasting 300 seconds, and a 25 second wait before new events in a completely propagated queue are propagated.

Unscheduling a Propagation Job

You can unschedule a propagation job using the UNSCHEDULE_PROPAGATION procedure in the DBMS_AQADM package. If there is a problem with a propagation job, then unscheduling and scheduling the propagation job may correct the problem.

For example, the following procedure unschedules a propagation job that propagates events from the strmadmin.strm01_queue source queue using the dbs2.net database link:

Specifying the Rule Set for a Propagation Job

You specify the rule set that you want to associate with a propagation job using the rule_set_name parameter in the ALTER_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. For example, the following procedure sets the rule set for a propagation job named strm01_propagation to strm02_rule_set.

Creates a propagation job named strm01_propagation. The propagation job is created only if it does not already exist.

Specifies that the propagation job propagates LCRs from strm01_queue in the current database to strm02_queue in the dbs2.net database

Specifies that the propagation job uses the dbs2.net database link to propagate the LCRs, because the destination_queue_name parameter contains @dbs2.net

Creates two rules. One rule specifies that the propagation job propagates row LCRs that contain the results of DML changes to the hr.locations table, and the other rule specifies that the propagation job propagates DDL LCRs that contain changes to the hr.locations table. The rule names are specified by the system.

Adds the two rules to the rule set associated with the propagation job

Specifies that the source database of the LCRs to be propagated is dbs1.net, which may or may not be the current database

Removing a Rule from the Rule Set for a Propagation Job

You specify that you want to remove a rule from the rule set for an existing propagation job by running the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package. For example, the following procedure removes a rule named DEPARTMENTS3 from the rule set of a propagation job named strm01_propagation.

In this example, the drop_unused_rule parameter in the REMOVE_RULE procedure is set to true, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule parameter is set to false, then the rule is removed from the rule set, but it is not dropped from the database.

In addition, if you want to remove all of the rules in the rule set for the propagation job, then specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

Note:

If you drop all of the rules in the rule set for a propagation job, then the propagation job propagations no events in the source queue to the destination queue.

Removing the Rule Set for a Propagation Job

You specify that you want to remove the rule set from a propagation job by setting the rule_set_name parameter to NULL in the ALTER_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. For example, the following procedure removes the rule set from a propagation job named strm01_propagation.

Managing a Streams Messaging Environment

Streams enables messaging with queues of type SYS.AnyData. These queues stage user messages whose payloads are of SYS.AnyData type, and a SYS.AnyData payload can be a wrapper for payloads of different datatypes.

This section provides instructions for completing the following tasks:

Wrapping User Messages in a SYS.AnyData Wrapper

You can wrap almost any type of payload in a SYS.AnyData payload. The following sections provide examples enqueuing messages into, and dequeuing messages from, a SYS.AnyData queue. These examples assume that you have configured a Streams administrator at each database.

To dequeue messages, you must know the consumer of the messages. To find the consumer for the messages in a queue, connect as the owner of the queue and query the AQ$queue_table_name, where queue_table_name is the name of the queue table. For example, to find the consumers of the messages in the oe_q_any queue, run the following query:

Create a procedure that takes as an input the consumer of the messages you want to dequeue. The following example procedure dequeues messages of oe.cust_address_typ and prints the contents of the messages.

Run the procedure you created in Step 1 and specify the consumer of the messages you want to dequeue, as in the following example:

SET SERVEROUTPUT ON SIZE 100000
EXEC oe.get_cust_address('OE');

Propagating Messages Between a SYS.AnyData Queue and a Typed Queue

SYS.AnyData queues can interoperate with typed queues in a Streams environment. A typed queue is a queue that can stage messages of a particular type only. To propagate a message from a SYS.AnyData queue to a typed queue, the message must be transformed to match the type of the typed queue. The following sections provide examples of propagating non-LCR user messages and LCRs between a SYS.AnyData queue and a typed queue.

Example of Propagating Non-LCR User Messages to a Typed Queue

The following steps set up propagation from a SYS.AnyData queue named oe_q_any to a typed queue of type oe.cust_address_typ named oe_q_address. The source queue oe_q_any is at the dbs1.net database, and the destination queue oe_q_address is at the dbs2.net database.

Create a database link between dbs1.net and dbs2.net if one does not already exist.

Create a subscriber for the typed queue if one does not already exist. The subscriber must contain a rule that ensures that only messages of the appropriate type are propagated to the destination queue.

Example of Propagating LCRs to a Typed Queue

To propagate LCRs from a SYS.AnyData queue to a typed queue, you complete the same steps as you do for non-LCR events, but Oracle supplies the transformation functions. You can use the following functions in the DBMS_STREAMS package to transform LCRs in SYS.AnyData queues to messages in typed queues:

You should only propagate user-enqueued LCRs to a typed queue. Do not propagate captured LCRs to a typed queue.

The following example sets up propagation of row LCRs from a SYS.AnyData queue named oe_q_any to a typed queue of type SYS.LCR$_ROW_RECORD named oe_q_lcr. The source queue oe_q_any is at the dbs1.net database, and the destination queue oe_q_lcr is at the dbs3.net database.

Create a database link between dbs1.net and dbs3.net if one does not already exist.