A row LCR can also contain transaction control statements. These row LCRs contain transaction control directives such as COMMIT and ROLLBACK.

Such row LCRs are internal and can be used by outbound servers, inbound servers, and XStream client applications to maintain transaction consistency.

Several PL/SQL procedures in the DBMS_XSTREAM_ADM package can create system-generated rules.

There are three types of procedures that create system-created rules:

• Procedures that create or alter an outbound server and the rules for the outbound server

  These procedures include CREATE_OUTBOUND, ADD_OUTBOUND, and ALTER_OUTBOUND. These procedures make it easy to configure XStream Out quickly. If they meet your needs, then you should use these procedures to simplify XStream Out configuration. The CREATE_OUTBOUND procedure creates the queue and capture process used by the outbound server in addition to the outbound server.

• Procedures that create a propagation or add rules to an existing propagation

  These procedures include the ADD_*_PROPAGATION_RULES procedures. If the specified propagation does not exist, then these procedures create the propagation and add rules to the propagation's rule sets. If the specified propagation exists, then these procedures add rules to the existing propagation's rule sets.

• Procedures that add rules to an existing XStream component, such as a capture process, outbound server, or inbound server

  These procedures include the ADD_*_RULES procedures. These procedure provide more flexibility and fine-grained control over the system-created rules. You should use these procedures when necessary to add rules to your XStream configuration.

The following table describes which procedures can create rules for which XStream components.

When you use a rule to specify a task that is relevant to an entire database, you are specifying a global rule.

You can specify a global rule for DML changes, a global rule for DDL changes, or a global rule for each type of change (two rules total).

A single global rule in the positive rule set for a capture process means that the capture process captures the results of either all DML changes or all DDL changes to the source database. A single global rule in the negative rule set for a capture process means that the capture process discards the results of either all DML changes or all DDL changes to the source database.

A single global rule in the positive rule set for a propagation means that the propagation rule controls the set of LCRs that are applicable to a specific outbound server. If a single capture services multiple outbound servers, the set of changes distributed to each outbound server is controlled by the propagation rules (the capture rules are the superset of all changes). A single global rule in the negative rule set for a propagation means that the propagation discards either all row LCRs or all DDL LCRs from the capture process.

A single global rule in the positive rule set for an outbound server means that the outbound server sends either all row LCRs or all DDL LCRs that it receives to an XStream client application. A single global rule in the negative rule set for an outbound server means that the outbound server discards either all row LCRs or all DDL LCRs that it receives.

A single global rule in the positive rule set for an inbound server means that the inbound server applies either all row LCRs or all DDL LCRs sent to it by the XStream client application. A single global rule in the negative rule set for an inbound server means that the inbound server discards either all row LCRs or all DDL LCRs sent to it by the XStream client application.

When an inbound server should apply all of the LCRs it receives from its client application, you can configure the inbound server with no rule sets instead of using global rules. Also, for an inbound server to perform best, it should not receive LCRs that it should not apply.

To specify global rules for an outbound server, use the ALTER_OUTBOUND procedure or, for specifying a greater level of detail, the ADD_GLOBAL_RULES procedure in the DBMS_XSTREAM_ADM package.

To specify global rules for an inbound server, use the ALTER_INBOUND procedure or, for specifying a greater level of detail, the ADD_GLOBAL_RULES procedure in the DBMS_XSTREAM_ADM package.

When you use a rule to specify a task that is relevant to a schema, you are specifying a schema rule.

You can specify a schema rule for DML changes, a schema rule for DDL changes, or a schema rule for each type of change to the schema (two rules total).

A single schema rule in the positive rule set for a capture process means that the capture process captures either the DML changes or the DDL changes to the schema. A single schema rule in the negative rule set for a capture process means that the capture process discards either the DML changes or the DDL changes to the schema.

A single schema rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema. A single schema rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema.

A single schema rule in the positive rule set for an outbound server means that the outbound server sends either the row LCRs or the DDL LCRs that it receives that contain changes to the schema to an XStream client application. A single schema rule in the negative rule set for an outbound server means that the outbound server discards either the row LCRs or the DDL LCRs that it receives that contain changes to the schema.

A single schema rule in the positive rule set for an inbound server means that the inbound server applies either the row LCRs or the DDL LCRs that it receives from an XStream client application that contain changes to the schema. A single schema rule in the negative rule set for an inbound server means that the inbound server discards either the row LCRs or the DDL LCRs that it receives from an XStream client application that contain changes to the schema.

To specify schema rules for either an outbound server or an inbound server, use the ALTER_OUTBOUND procedure or the ADD_SCHEMA_RULES procedure in the DBMS_XSTREAM_ADM package.

When you use a rule to specify a task that is relevant to a table, you are specifying a table rule.

You can specify a table rule for DML changes, a table rule for DDL changes, or a table rule for each type of change to the table (two rules total).

A single table rule in the positive rule set for a capture process means that the capture process captures either the DML changes or the DDL changes to the table. A single table rule in the negative rule set for a capture process means that the capture process discards either the DML changes or the DDL changes to the table.

A single table rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the table. A single table rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the table.

A single table rule in the positive rule set for an outbound server means that the outbound server sends either the row LCRs or the DDL LCRs that it receives that contain changes to the table to an XStream client application. A single table rule in the negative rule set for an outbound server means that the outbound server discards either the row LCRs or the DDL LCRs that it receives that contain changes to the table.

A single table rule in the positive rule set for an inbound server means that the inbound server applies either the row LCRs or the DDL LCRs that it receives from an XStream client application that contain changes to the table. A single table rule in the negative rule set for an inbound server means that the inbound server discards either the row LCRs or the DDL LCRs that it receives from an XStream client application that contain changes to the table.

To specify table rules for an outbound server or inbound server, use either the ALTER_OUTBOUND procedure or ADD_TABLE_RULES in the DBMS_XSTREAM_ADM package.

A subset rule is a special type of table rule for DML changes that is relevant only to a subset of the rows in a table.

When you create a subset rule, you use a condition similar to a WHERE clause in a SELECT statement to specify the following:

• That a capture process only captures a subset of the row changes resulting from DML changes to a particular table

• That a propagation only propagates a subset of the row LCRs relating to a particular table

• That an outbound server only sends a subset of the row LCRs relating to a particular table to an XStream client application

• That an inbound server only applies a subset of the row LCRs relating to a particular table

Supplemental logging is required when you specify the following types of subset rules:

• Subset rules for a capture process

• Subset rules for a propagation that will propagate LCRs captured by a capture process

• Subset rules for an outbound server that will send LCRs captured by a capture process to an XStream client application

In any of these cases, an unconditional supplemental log group must be specified at the source database for all the columns in the subset condition. In some cases, when a subset rule is specified, an update can be converted to an insert, and, in these cases, supplemental information might be needed for some or all of the columns.

To specify subset rules for an outbound server, use the ADD_SUBSET_OUTBOUND_RULES, ADD_SUBSET_RULES, or the REMOVE_SUBSET_OUTBOUND_RULES procedures in the DBMS_XSTREAM_ADM package.

A multitenant environment enables an Oracle database to contain a portable set of schemas, objects, and related structures that appears logically to an application as a separate database. This self-contained collection is called a pluggable database (PDB). A CDB contains PDBs.

It can also contain application containers. An application container is an optional component of a CDB that consists of an application root and the application PDBs associated with it. An application container stores data for one or more applications. An application container shares application metadata and common data. In a CDB, each of the following is a container: the CDB root, each PDB, each application root, and each application PDB.

In a CDB, LCRs can contain the global name of the container where the change originated in the source_database_name attribute and the global name of the CDB root in the root_name attribute. The rules for XStream components can consider these attributes.

• System-Created Rules in a CDB and XStream Out
  In a CDB, XStream Out must be configured in the CDB root. Therefore, the PL/SQL procedures in the DBMS_XSTREAM_ADM package that create system-created rules must be run in the CDB root while connected as a common user.
• System-Created Rules in a CDB and XStream In
  You can configure XStream In in the root or in any container in a CDB.

In addition to declarative rule-based transformations, a row migration is an internal transformation that takes place when a subset rule evaluates to TRUE.

You can use the DBMS_XSTREAM_ADM.ADD_SUBSET_RULES procedure to add subset rules. If both types of transformations are specified for a single rule, then Oracle Database performs the transformations in the following order when the rule evaluates to TRUE:

1. Row migration

2. Declarative rule-based transformation

If you do not want to use the default declarative rule-based transformation ordering for a particular rule, then you can specify step numbers for each declarative transformation specified for the rule.

If you specify a step number for one or more declarative transformations for a particular rule, then the declarative transformations for the rule behave in the following way:

• Declarative transformations are performed in order of increasing step number.

• The default step number for a declarative transformation is 0 (zero). A declarative transformation uses this default if no step number is explicitly specified for it.

• If two or more declarative transformations have the same step number, then these declarative transformations follow the default ordering described in "Declarative Rule-Based Transformation Ordering".

For example, you can reverse the default ordering for declarative transformations by specifying the following step numbers for transformations associated with a particular rule:

• Keep columns with step number 6

• Delete column with step number 5

• Rename column with step number 4

• Add column with step number 3

• Rename table with step number 2

• Rename schema with step number 1

With this ordering specified, rename schema transformations are performed first, and delete column transformations are performed last.

Several considerations apply to declarative rule-based transformations.

These considerations include the following:

• For a rule-based transformation to be performed by an XStream component, the rule must be in the positive rule set for the XStream component. If the rule is in the negative rule set for the XStream component, then the XStream component ignores the rule-based transformation.

• Rule-based transformations are different from transformations performed using the DBMS_TRANSFORM package. This document does not discuss transformations performed with the DBMS_TRANSFORM package.

• If a large percentage of row LCRs will be transformed in an XStream In configuration, you can use DML handlers with XStream In. Be aware that this method may not perform as well as making the changes in the XStream In client application. If you are performing multiple or complex transformations on row LCRs in an XStream In configuration, then consider reducing the XStream In processing time by making these modifications in the client application prior to sending the changes to XStream In.

There are several XStream Out apply subcomponents types.

The following subcomponent types are possible:

• PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process to an outbound server where the capture process and outbound server are in different databases.

• APPLY READER for a reader server. APPLY READER receives LCRs from the capture process, organizes them into transactions, does dependency calculations, and passes the LCRs to the apply coordinator.

• APPLY COORDINATOR for a coordinator process. It takes the transactions from the capture process, uses the dependency information to determine how to schedule the transactions and sends the LCRs to the apply server.

• APPLY SERVER for an apply server. It delivers the LCRs to the client application.

There are several XStream In apply subcomponents types.

The following subcomponent types are possible:

• APPLY READER for a reader server. It takes the LCRs from client application converts them into transactions, checks the transactional order and does dependency calculations.

• APPLY COORDINATOR for a coordinator process. It takes the transactions from the reader server, uses the dependency information to determine how to schedule the transactions and sends the LCRs to the apply server.

• APPLY SERVER for an apply server. It applies the LCRs to an apply handler. If the LCR cannot be applied, it is placed into an error queue.

You can collect XStream statistics with the UTL_RPADV package.

1. Identify the database that you will use to gather the information. An administrative user at this database must meet the following requirements:

   • If you want to gather XStream statistics for more than one database, the user must have access to a database link to each database that contains XStream components to monitor.

   • The user must have been granted privileges using the DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE procedure. If you want to gather XStream statistics for more than one database, each database link must connect to a user at the remote database that has been granted privileges using the DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE procedure.

     If you configure an XStream administrator at each database with XStream components, then the XStream administrator has the necessary privileges.

   • The user must have the necessary privileges to create tables and procedures. If you want to gather XStream statistics for more than one database, each database link must connect to a user at the remote database that has the necessary privileges to create tables and procedures.

   If no database in your environment meets these requirements, then choose a database, configure the necessary database links, and grant the necessary privileges to the users before proceeding.

2. In SQL*Plus, connect to the database you identified in Step 1 as a user that meets the requirements listed in Step 1.

   See Oracle Database Administrator’s Guide for information about connecting to a database in SQL*Plus.

3. Run the utlrpadv.sql script in the rdbms/admin directory in ORACLE_HOME to load the UTL_RPADV package. For example:

   @utlrpadv.sql
   

4. Either collect the current XStream performance statistics once, or create a job that continually monitors XStream performance:

   • To collect the current XStream performance statistics once, run the COLLECT_STATS procedure:

     exec UTL_RPADV.COLLECT_STATS
     

     This example uses the default values for the parameters in the COLLECT_STATS procedure. Therefore, this example runs the Performance Advisor 10 times with 60 seconds between each run. These values correspond with the default values for the num_runs and interval parameters, respectively, in the COLLECT_STATS procedure.

   • To create a job that continually monitors XStream performance:

     exec UTL_RPADV.START_MONITORING
     

     This example creates a monitoring job, and the monitoring job gathers performance statistics continually at set intervals. This example uses the default values for the parameters in the START_MONITORING procedure. Therefore, this example runs the Performance Advisor every 60 seconds. This value corresponds with the default value for the interval parameter in the START_MONITORING procedure. If an interval is specified in the START_MONITORING procedure, then the specified interval is used for the interval parameter in the COLLECT_STATS procedure.

   These procedures include several parameters that you can use to adjust the way performance statistics are gathered. See Oracle Database PL/SQL Packages and Types Reference for more information.

The SHOW_STATS procedure in the UTL_RPADV package displays the statistics that the Performance Advisor gathered and stored.

1. Collect statistics by completing the steps described in "Collecting XStream Statistics Using the UTL_RPADV Package".
2. Connect to the database as the user who collected the statistics.
3. If you are using a monitoring job, then query the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view to determine the name of this table that stores the statistics:

   SELECT SHOW_STATS_TABLE FROM STREAMS$_PA_MONITORING;
   

4. Run the SHOW_STATS procedure.

   For example, if you are using a monitoring job and the default storage table, then run the following procedure:

   SET SERVEROUTPUT ON SIZE 50000
   BEGIN
     UTL_RPADV.SHOW_STATS(
       path_stat_table => 'STREAMS$_PA_SHOW_PATH_STAT');
   END;
   /
   

There are differences between the output for apply processes and the output for XStream outbound servers and inbound servers.

• Sample Output When an Outbound Server Is the Last Component in a Path
  Here is sample output for when an outbound server is the last component in a path.
• Sample Output When an Inbound Server Is the Last Component in a Path
  Here is sample output for when an inbound server is the last component in a path.

The SHOW_STATS_HTML procedure in the UTL_RPADV package creates an HTML report that contains the statistics that the Performance Advisor gathered and stored.

1. Collect statistics by completing the steps described in "Collecting XStream Statistics Using the UTL_RPADV Package".
2. Connect to the database as the user who collected the statistics.
3. If you are using a monitoring job, then query the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view to determine the name of this table that stores the statistics:

   SELECT SHOW_STATS_TABLE FROM STREAMS$_PA_MONITORING;
   

4. Create a directory object for the directory that will contain the files in the HTML report.

   For example, to create a directory object named XSTREAM_STATS_HTML for the /usr/xstream/reports directory, run the following SQL statement:

   CREATE DIRECTORY XSTREAM_STATS_HTML AS '/usr/xstream/reports';
   

5. Run the SHOW_STATS_HTML procedure.

   For example, if you are using a monitoring job and the default storage table, then run the following procedure:

   BEGIN
     UTL_RPADV.SHOW_STATS_HTML(
       directory       => 'XSTREAM_STATS_HTML',
       reportname      => 'xstream_stats.html',
       comp_stat_table => 'STREAMS$_ADVISOR_COMP_STAT');
   END;
   /
   

The SHOW_STATS_HTML procedure in the UTL_RPADV package can generate the same output as the SHOW_STATS procedure, but it formats the output as HTML in HTML files.

The SHOW_STATS_HTML output is easier to read than the SHOW_STATS output. For example, the procedure generates multiple files, and each file begins with the report name. The report includes tables with the performance statistics. Statistics for different paths are in different rows in these tables, and you can click on a path for more detailed statistics about the path. The report_name parameter indicates the master HTML file with links to the other HTML files.

The following are considerations for using the SHOW_STATS_HTML procedure:

• The default table that stores the statistics is STREAMS$_ADVISOR_COMP_STAT. The SHOW_STATS procedure uses a different default table (STREAMS$_ADVISOR_PATH_STAT).

• You must specify a directory object in the directory parameter of the procedure. This directory stores the HTML files generated by the procedure.

  The specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes the procedure must have READ and WRITE privilege on the directory.

An XStream outbound server or inbound server performs implicit data type conversion where it is possible, and the generated SQL follows ANSI standards where it is possible.

The following are considerations for automatic data type conversions:

• NULL is specified as "NULL".

• Single quotation marks are converted into double quotation marks for the following data types when they are inline values: CHAR, VARCHAR2, NVARCHAR2, NCHAR, CLOB, and NCLOB.

• LONG data is converted into CLOB data.

• LONG RAW data is converted into BLOB data.

For INSERT and UPDATE operations on LOB columns, an outbound server automatically assembles the LOB chunks using LOB assembly.

For these operations, the generated SQL includes a non-NULL empty value. The actual values of the chunked columns arrive in subsequent LCRs. For each chunk, you must perform the correct SQL operation on the correct column.

Similarly, for LONG, LONG RAW, and XMLType data types, an outbound server generates a non-NULL empty value, and the actual values of the column arrive in chunks in subsequent LCRs. For each chunk, you must perform the correct SQL operation on the correct column.

In the inline version of the generated SQL, for LOB, LONG, LONG RAW, and XMLType data type columns, the following SQL is generated for inserts and updates:

• For CLOB, NCLOB, and LONG data type columns:

  EMPTY_CLOB()
  

• For BLOB and LONG RAW data type columns:

  EMPTY_BLOB()
  

• For XMLType columns:

  XMLTYPE.CREATEXML('xml /')
  

  where xml / is the XML chunk.

After the LCR that contains the DML statement arrives, the data for these changes arrive in separate chunks. You can generate the WHERE clause for such a change and use the generated WHERE clause to identify the row for the modifications contained in the chunks. For example, in PL/SQL you can use the GET_WHERE_CLAUSE row LCR member procedure to generate the WHERE clause for a row change.

For INSERT and UPDATE operations, the generated WHERE clause identifies the row after the insert or update. For example, consider the following update to the hr.departments table:

UPDATE hr.departments SET department_name='Management' 
  WHERE department_name='Administration';

The generated WHERE clause for this change is the following:

WHERE "DEPARTMENT_NAME"='Management'

For piecewise LOB operation performed by subprograms in the DBMS_LOB package (including the WRITE, TRIM, and ERASE procedures), the generated SQL includes a SELECT FOR UPDATE statement.

For example, a LOB_WRITE operation on a clob_col results in generated SQL similar to the following:

SELECT "CLOB_COL" FROM "HR"."LOB_TAB" WHERE "N1"=2 FOR UPDATE

The selected clob_col must be defined. You can use the LOB locator to perform piecewise LOB operations with the LOB chunks that follow the row LCR.

Examples illustrate SQL statements generated by an outbound server for changes made to the hr.employees table.

INSERT INTO hr.employees (employee_id, 
                           last_name, 
                           email, 
                           hire_date, 
                           job_id, 
                           salary, 
                           commission_pct) 
                   VALUES (207, 
                           'Gregory', 
                           'pgregory@example.com', 
                           SYSDATE, 
                           'PU_CLERK', 
                           9000, 
                           NULL);

INSERT INTO "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME",
"EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT",
"MANAGER_ID","DEPARTMENT_ID" ) VALUES ( 207, NULL,'Gregory',
'pgregory@example.com', NULL , TO_DATE(' 2009-04-15','syyyy-mm-dd'),
'PU_CLERK',9000, NULL , NULL , NULL )

INSERT INTO "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME",
"EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY",
"COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" ) VALUES ( :1   ,:2   ,:3   
,:4   ,:5   ,:6   ,:7   ,:8   ,:9   ,:10  ,:11  )

UPDATE hr.employees SET salary=10000 WHERE employee_id=207;

UPDATE "HR"."EMPLOYEES" SET "SALARY"=10000 WHERE "EMPLOYEE_ID"=207 
AND "SALARY"=9000

UPDATE "HR"."EMPLOYEES" SET "SALARY"=:1    WHERE "EMPLOYEE_ID"=:2    
AND "SALARY"=:3

DELETE FROM hr.employees WHERE employee_id=207;

DELETE  FROM "HR"."EMPLOYEES" WHERE "EMPLOYEE_ID"=207 AND "FIRST_NAME" IS NULL 
AND "LAST_NAME"='Gregory' AND "EMAIL"='pgregory@example.com' AND 
"PHONE_NUMBER" IS NULL  AND "HIRE_DATE"= TO_DATE(' 2009-04-15','syyyy-mm-dd') 
AND "JOB_ID"='PU_CLERK' AND "SALARY"=10000 AND "COMMISSION_PCT" IS NULL  
AND "MANAGER_ID" IS NULL  AND "DEPARTMENT_ID" IS NULL 

DELETE  FROM "HR"."EMPLOYEES" WHERE "EMPLOYEE_ID"=:1    AND "FIRST_NAME"=:2    
AND "LAST_NAME"=:3    AND "EMAIL"=:4    AND "PHONE_NUMBER"=:5    AND 
"HIRE_DATE"=:6    AND "JOB_ID"=:7    AND "SALARY"=:8    AND 
"COMMISSION_PCT"=:9   AND "MANAGER_ID"=:10   AND "DEPARTMENT_ID"=:11 

Examples illustrate SQL statements generated by an outbound server for changes made ti a table with LOB columns.

Examples illustrate SQL statements generated by an outbound server for changes made to the following table:

CREATE TABLE hr.lob_tab(
   n1        number primary key,
   clob_col  CLOB,
   nclob_col NCLOB,
   blob_col  BLOB);

The GET_WHERE_CLAUSE member procedure generates the following WHERE clause for this insert:

• Inline:

  WHERE "N1"=2
  

• Bind variables:

  WHERE "N1"=:1
  

You can use the WHERE clause to identify the row that was inserted when the subsequent chunks arrive for the LOB column change.

INSERT INTO hr.lob_tab VALUES (2, 'test insert', NULL, NULL);

INSERT INTO "HR"."LOB_TAB"("N1","BLOB_COL","CLOB_COL","NCLOB_COL" ) 
VALUES ( 2,, EMPTY_CLOB() ,)

INSERT INTO "HR"."LOB_TAB"("N1","BLOB_COL","CLOB_COL","NCLOB_COL" ) 
VALUES ( :1   ,:2   ,:3   ,:4   )

UPDATE hr.lob_tab SET clob_col='test update' WHERE n1=2;

UPDATE "HR"."LOB_TAB" SET "CLOB_COL"= EMPTY_CLOB()  WHERE "N1"=2

UPDATE "HR"."LOB_TAB" SET "CLOB_COL"=:1    WHERE "N1"=:2

DELETE FROM hr.lob_tab WHERE n1=2;

DELETE  FROM "HR"."LOB_TAB" WHERE "N1"=2

DELETE  FROM "HR"."LOB_TAB" WHERE "N1"=:1