29 Monitoring Other Oracle Streams Components

This chapter provides sample queries that you can use to monitor various Oracle Streams components.

The following topics describe monitoring various Oracle Streams components:

Monitoring Oracle Streams Administrators and Other Oracle Streams Users
Monitoring the Oracle Streams Pool
Monitoring Compatibility in an Oracle Streams Environment
Monitoring Oracle Streams Performance Using AWR and Statspack

Note:

The Oracle Streams tool in Oracle Enterprise Manager is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information.

See Also:

Oracle Database Reference for information about the data dictionary views described in this chapter

Monitoring Oracle Streams Administrators and Other Oracle Streams Users

The following sections contain queries that you can run to list Oracle Streams administrators and other users who allow access to remote Oracle Streams administrators:

Listing Local Oracle Streams Administrators
Listing Users Who Allow Access to Remote Oracle Streams Administrators

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about configuring Oracle Streams administrators and other Oracle Streams users using the DBMS_STREAMS_AUTH package

Listing Local Oracle Streams Administrators

You can grant privileges to a local Oracle Streams administrator by running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. The DBA_STREAMS_ADMINISTRATOR data dictionary view contains only the local Oracle Streams administrators created with the grant_privileges parameter set to TRUE when the GRANT_ADMIN_PRIVILEGE procedure was run for the user. If you created an Oracle Streams administrator using generated scripts and set the grant_privileges parameter to FALSE when the GRANT_ADMIN_PRIVILEGE procedure was run for the user, then the DBA_STREAMS_ADMINISTRATOR data dictionary view does not list the user as an Oracle Streams administrator.

To list the local Oracle Streams administrators created with the grant_privileges parameter set to TRUE when running the GRANT_ADMIN_PRIVILEGE procedure, run the following query:

COLUMN USERNAME HEADING 'Local Streams Administrator' FORMAT A30

SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR
  WHERE LOCAL_PRIVILEGES = 'YES';

Your output looks similar to the following:

Local Streams Administrator
------------------------------
STRMADMIN

The GRANT_ADMIN_PRIVILEGE might not have been run on a user who is an Oracle Streams administrator. Such administrators are not returned by the query in this section. Also, you can change the privileges for the users listed after the GRANT_ADMIN_PRIVILEGE procedure has been run for them. The DBA_STREAMS_ADMINISTRATOR view does not track these changes unless they are performed by the DBMS_STREAMS_AUTH package. For example, you can revoke the privileges granted by the GRANT_ADMIN_PRIVILEGE procedure for a particular user using the REVOKE SQL statement, but this user would be listed when you query the DBA_STREAMS_ADMINISTRATOR view.

Oracle recommends using the REVOKE_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package to revoke privileges from a user listed by the query in this section. When you revoke privileges from a user using this procedure, the user is removed from the DBA_STREAMS_ADMINISTRATOR view.

See Also:

Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator

Listing Users Who Allow Access to Remote Oracle Streams Administrators

You can configure a user to allow access to remote Oracle Streams administrators by running the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package. Such a user allows the remote Oracle Streams administrator to perform administrative actions in the local database using a database link.

Typically, you configure such a user at a local source database if a downstream capture process captures changes originating at the local source database. The Oracle Streams administrator at a downstream capture database administers the source database using this connection.

To list the users who allow to remote Oracle Streams administrators, run the following query:

COLUMN USERNAME HEADING 'Users Who Allow Remote Access' FORMAT A30

SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR
  WHERE ACCESS_FROM_REMOTE = 'YES';

Your output looks similar to the following:

Users Who Allow Remote Access
------------------------------
STRMREMOTE

Monitoring the Oracle Streams Pool

The Oracle Streams pool is a portion of memory in the System Global Area (SGA) that is used by Oracle Streams. The Oracle Streams pool stores enqueued messages in memory, and it provides memory for capture processes and apply processes. The Oracle Streams pool always stores LCRs captured by a capture process, and it can store other types of messages that are enqueued manually into a buffered queue.

The Oracle Streams pool size is managed automatically when the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value. If these parameters are all set to 0 (zero), then you can specify the size of the Oracle Streams pool in bytes using the STREAMS_POOL_SIZE initialization parameter. In this case, the V$STREAMS_POOL_ADVICE dynamic performance view provides information about an appropriate setting for the STREAMS_POOL_SIZE initialization parameter.

This section contains example queries that show when you should increase, retain, or decrease the size of the Oracle Streams pool. Each query shows the following information about the Oracle Streams pool:

STREAMS_POOL_SIZE_FOR_ESTIMATE shows the size, in megabytes, of the Oracle Streams pool for the estimate. The size ranges from values smaller than the current Oracle Streams pool size to values larger than the current Oracle Streams pool size, and there is a separate row for each increment. There always is an entry that shows the current Oracle Streams pool size, and there always are 20 increments. The range and the size of the increments depend on the current size of the Oracle Streams pool.
STREAMS_POOL_SIZE_FACTOR shows the size factor of an estimate as it relates to the current size of the Oracle Streams pool. For example, a size factor of.2 means that the estimate is for 20% of the current size of the Oracle Streams pool, while a size factor of 1.6 means that the estimate is for 160% of the current size of the Oracle Streams pool. The row with a size factor of 1.0 shows the current size of the Oracle Streams pool.
ESTD_SPILL_COUNT shows the estimated number messages that will spill from memory to the queue table for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_SPILL_TIME shows the estimated elapsed time, in seconds, spent spilling messages from memory to the queue table for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_UNSPILL_COUNT shows the estimated number messages that will unspill from the queue table back into memory for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_UNSPILL_TIME shows the estimated elapsed time, in seconds, spent unspilling messages from the queue table back into memory for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.

If any capture processes, propagations, or apply processes are disabled when you query the V$STREAMS_POOL_ADVICE view, and you plan to enable them in the future, then ensure that you consider the memory resources required by these Oracle Streams clients before you decrease the size of the Oracle Streams pool.

Tips:

In general, the best size for the Oracle Streams pool is the smallest size for which spilled and unspilled messages and times are close to zero.
For the most accurate results, you should run a query on the V$STREAMS_POOL_ADVICE view when there is a typical amount of dequeue activity by propagations and apply processes in a database. If dequeue activity is far lower than typical, or far higher than typical, then the query results might not be a good guide for adjusting the size of the Oracle Streams pool.

See Also:

Oracle Streams Replication Administrator's Guide for information about configuring the Oracle Streams pool
Oracle Streams Replication Administrator's Guide for more information about the STREAMS_POOL_SIZE initialization parameter

Query Result that Advises Increasing the Oracle Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE HEADING 'Oracle Streams Pool Size|for Estimate(MB)'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99

SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                         Oracle Streams Pool Estimated    Estimated Estimated
Oracle Streams Pool Size                Size     Spill        Spill   Unspill
        for Estimate(MB)              Factor     Count         Time     Count
------------------------ ------------------- --------- ------------ ---------
               24           .1       158        62.00         0          .00
               48           .2       145        59.00         0          .00
               72           .3       137        53.00         0          .00
               96           .4       122        50.00         0          .00
              120           .5       114        48.00         0          .00
              144           .6       103        45.00         0          .00
              168           .7        95        39.00         0          .00
              192           .8        87        32.00         0          .00
              216           .9        74        26.00         0          .00
              240          1.0        61        21.00         0          .00
              264          1.1        56        17.00         0          .00
              288          1.2        43        15.00         0          .00
              312          1.3        36        11.00         0          .00
              336          1.4        22         8.00         0          .00
              360          1.5         9         2.00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, 384 megabytes, or 160% of the size of the current Oracle Streams pool, is the optimal size for the Oracle Streams pool. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Note:

After you adjust the size of the Oracle Streams pool, it might take some time for the new size to result in new output for the V$STREAMS_POOL_ADVICE view.

Query Result that Advises Retaining the Current Oracle Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Oracle Streams Pool|Size for Estimate'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 
SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                         Oracle Streams Pool Estimated    Estimated Estimated
Oracle Streams Pool Size                Size     Spill        Spill   Unspill
        for Estimate(MB)              Factor     Count         Time     Count
------------------------ ------------------- --------- ------------ ---------
               24           .1        89        52.00         0          .00
               48           .2        78        48.00         0          .00
               72           .3        71        43.00         0          .00
               96           .4        66        37.00         0          .00
              120           .5        59        32.00         0          .00
              144           .6        52        26.00         0          .00
              168           .7        39        20.00         0          .00
              192           .8        27        12.00         0          .00
              216           .9        15         5.00         0          .00
              240          1.0         0          .00         0          .00
              264          1.1         0          .00         0          .00
              288          1.2         0          .00         0          .00
              312          1.3         0          .00         0          .00
              336          1.4         0          .00         0          .00
              360          1.5         0          .00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, the current size of the Oracle Streams pool is the optimal size. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Query Result that Advises Decreasing the Oracle Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Oracle Streams Pool|Size for Estimate'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 
SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                         Oracle Streams Pool Estimated    Estimated Estimated
Oracle Streams Pool Size                Size     Spill        Spill   Unspill
        for Estimate(MB)              Factor     Count         Time     Count
------------------------ ------------------- --------- ------------ ---------
               24           .1       158        62.00         0          .00
               48           .2       145        59.00         0          .00
               72           .3       137        53.00         0          .00
               96           .4       122        50.00         0          .00
              120           .5       114        48.00         0          .00
              144           .6       103        45.00         0          .00
              168           .7         0          .00         0          .00
              192           .8         0          .00         0          .00
              216           .9         0          .00         0          .00
              240          1.0         0          .00         0          .00
              264          1.1         0          .00         0          .00
              288          1.2         0          .00         0          .00
              312          1.3         0          .00         0          .00
              336          1.4         0          .00         0          .00
              360          1.5         0          .00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, 168 megabytes, or 70% of the size of the current Oracle Streams pool, is the optimal size for the Oracle Streams pool. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Note:

After you adjust the size of the Oracle Streams pool, it might take some time for the new size to result in new output for the V$STREAMS_POOL_ADVICE view.

Monitoring Compatibility in an Oracle Streams Environment

Some database objects and data types are not compatible with Oracle Streams capture processes, synchronous captures, and apply processes. If one of these Oracle Streams clients tries to process an unsupported database object or data type, errors result.

The queries in the following sections show Oracle Streams compatibility for database objects and columns in the local database:

Monitoring Compatibility for Capture Processes
Listing Database Objects and Columns Not Compatible with Synchronous Captures
Monitoring Compatibility for Apply Processes

Monitoring Compatibility for Capture Processes

This section contains these topics:

Listing the Database Objects That Are Not Compatible with Capture Processes
Listing the Database Objects Recently Compatible with Capture Processes

Listing the Database Objects That Are Not Compatible with Capture Processes

A database object is not compatible with capture processes if capture processes cannot capture changes to it. The query in this section displays the following information about database objects that are not compatible with capture processes:

The object owner
The object name
The reason why the object is not compatible with capture processes
Whether capture processes automatically filter out changes to the database object (AUTO_FILTERED column)

If capture processes automatically filter out changes to a database object, then the rule sets used by the capture processes do not need to filter them out explicitly. For example, capture processes automatically filter out changes to domain indexes. However, if changes to incompatible database objects are not filtered out automatically, then the rule sets used by the capture process must filter them out to avoid errors.

For example, suppose the rule sets for a capture process instruct the capture process to capture all of the changes made to a specific schema. Also suppose that the query in this section shows that one object in this schema is not compatible with capture processes, and that changes to the object are not filtered out automatically. In this case, you can add a rule to the negative rule set for the capture process to filter out changes to the incompatible database object.

Run the following query to list the database objects in the local database that are not compatible with capture processes:

COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A30
COLUMN REASON HEADING 'Reason' FORMAT A30
COLUMN AUTO_FILTERED HEADING 'Auto|Filtered?' FORMAT A9

SELECT OWNER, TABLE_NAME, REASON, AUTO_FILTERED FROM DBA_STREAMS_UNSUPPORTED;

Your output looks similar to the following:

Object                                                                 Auto
Owner    Object Name                    Reason                         Filtered?
-------- ------------------------------ ------------------------------ ---------
IX       AQ$_ORDERS_QUEUETABLE_G        column with user-defined type  NO
IX       AQ$_ORDERS_QUEUETABLE_H        unsupported column exists      NO
IX       AQ$_ORDERS_QUEUETABLE_I        unsupported column exists      NO
IX       AQ$_ORDERS_QUEUETABLE_L        AQ queue table                 NO
IX       AQ$_ORDERS_QUEUETABLE_S        AQ queue table                 NO
IX       AQ$_ORDERS_QUEUETABLE_T        AQ queue table                 NO
IX       AQ$_STREAMS_QUEUE_TABLE_C      AQ queue table                 NO
IX       AQ$_STREAMS_QUEUE_TABLE_G      column with user-defined type  NO
IX       AQ$_STREAMS_QUEUE_TABLE_H      unsupported column exists      NO
IX       AQ$_STREAMS_QUEUE_TABLE_I      unsupported column exists      NO
IX       AQ$_STREAMS_QUEUE_TABLE_L      AQ queue table                 NO
IX       AQ$_STREAMS_QUEUE_TABLE_S      AQ queue table                 NO
IX       AQ$_STREAMS_QUEUE_TABLE_T      AQ queue table                 NO
IX       ORDERS_QUEUETABLE              column with user-defined type  NO
IX       STREAMS_QUEUE_TABLE            column with user-defined type  NO
OE       ACTION_TABLE                   column with user-defined type  NO
OE       CATEGORIES_TAB                 column with user-defined type  NO
.
.
.

Notice that the Auto Filtered? column is YES for the sh.dr$sup_text_indx$i domain index. A capture process automatically filters out data manipulation language (DML) changes to this database object, even if the rules sets for a capture process instruct the capture process to capture changes to it. By default, a capture process also filters out data definition language (DDL) changes to these database objects. However, if you want to capture these DDL changes, then use the DBMS_CAPTURE_ADM.SET_PARAMETER procedure to set the set_autofiltered_table_ddl capture process parameter to N and configure the capture process rule sets to capture these DDL changes.

Because the Auto Filtered? column is NO for other database objects listed in the example output, capture processes do not filter out changes to these database objects automatically. If a capture process attempts to process changes to these unsupported database objects, then the capture process raises an error. However, you can avoid these errors by configuring rules sets that instruct the capture process not to capture changes to these unsupported objects.

Note:

The results of the query in this section depend on the compatibility level of the database. More database objects are incompatible with capture processes at lower compatibility levels. The COMPATIBLE initialization parameter controls the compatibility level of the database.
For capture processes, you cannot use rule-based transformations to exclude a column of an unsupported data type. The entire database object must be excluded to avoid capture errors.
The DBA_STREAMS_UNSUPPORTED view only pertains to capture processes in Oracle Database 11g Release 1 (11.1) and later databases. This view does not pertain to synchronous captures and apply processes.

See Also:

Chapter 5, "How Rules Are Used in Oracle Streams"
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_CAPTURE_ADM.SET_PARAMETER procedure

Listing the Database Objects Recently Compatible with Capture Processes

The query in this section displays the following information about database objects that have become compatible with capture processes in a recent release of Oracle Database:

The object owner
The object name
The reason why the object was not compatible with capture processes in previous releases of Oracle Database
The Oracle Database release in which the object became compatible with capture processes

Run the following query to display this information for the local database:

COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN REASON HEADING 'Reason' FORMAT A30
COLUMN COMPATIBLE HEADING 'Compatible' FORMAT A10

SELECT OWNER, TABLE_NAME, REASON, COMPATIBLE FROM DBA_STREAMS_NEWLY_SUPPORTED;

The following is a sample of the output from this query:

Owner      Object Name          Reason                         Compatible
---------- -------------------- ------------------------------ ----------
HR         COUNTRIES            IOT                            10.1
OE         WAREHOUSES           table with XMLType column      11.1
SH         CAL_MONTH_SALES_MV   materialized view              10.1
SH         FWEEK_PSCAT_SALES_MV materialized view              10.1

The Compatible column shows the minimum database compatibility for capture processes to support the database object. If the local database compatibility is equal to or higher than the value in the Compatible column for a database object, then capture processes can capture changes to the database object successfully. You control the compatibility of a database using the COMPATIBLE initialization parameter.

If your Oracle Streams environment includes databases that are running different versions of the Oracle Database, then you can configure rules that use the GET_COMPATIBLE member function for LCRs to filter out LCRs that are not compatible with particular databases. These rules can be added to the rule sets of capture processes, synchronous captures, propagations, and apply processes to filter out incompatible LCRs wherever necessary in a stream.

Note:

The DBA_STREAMS_NEWLY_SUPPORTED view only pertains to capture processes in Oracle Database 11g Release 1 (11.1) and later databases. This view does not pertain to synchronous captures and apply processes.

See Also:

Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
"Listing the Database Objects That Are Not Compatible with Capture Processes"
"Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs" for information about creating rules that use the GET_COMPATIBLE member function for LCRs

Listing Database Objects and Columns Not Compatible with Synchronous Captures

A database object or a column in a table is not compatible with synchronous captures if synchronous captures cannot capture changes to it. For example, synchronous captures cannot capture changes to object tables. Synchronous captures can capture changes to relational tables, but they cannot capture changes to columns of some data types.

The query in this section displays the following information about database objects and columns that are not compatible with synchronous captures:

The object owner
The object name
The column name
The reason why the column is not compatible with synchronous captures

To list the columns that are not compatible with synchronous captures in the local database, run the following query:

COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN SYNC_CAPTURE_REASON HEADING 'Synchronous|Capture Reason' FORMAT A25
 
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       SYNC_CAPTURE_REASON
 FROM DBA_STREAMS_COLUMNS
 WHERE SYNC_CAPTURE_VERSION IS NULL;

When a query on the DBA_STREAMS_COLUMNS view returns NULL for SYNC_CAPTURE_VERSION, it means that synchronous captures do not support the column. The WHERE clause in the query ensures that the query only returns columns that are not supported by synchronous captures.

The following is a sample of the output from this query:

Object                                             Synchronous
Owner    Object Name          Column Name          Capture Reason
-------- -------------------- -------------------- -------------------------
.
.
.
SH       SALES_TRANSACTIONS_E UNIT_COST            external table
         XT
OE       LINEITEM_TABLE       SYS_XDBPD$           object table
OE       LINEITEM_TABLE       ITEMNUMBER           object table
PM       PRINT_MEDIA          AD_FINALTEXT         table with nested table c
                                                   olumn
.
.
.

To avoid synchronous capture errors, configure the synchronous capture rule set to ensure that the synchronous capture does not try to capture changes to an unsupported database object, such as an object table. To avoid synchronous capture errors while capturing changes to relational tables, you have the following options:

Configure the synchronous capture rule set to ensure that the synchronous capture does not try to capture changes to a table that contains one or more unsupported columns.
Configure rule-based transformations to exclude columns that are not supported by synchronous captures.

Note:

Synchronous capture is available in Oracle Database 11g Release 1 (11.1) and later databases. It is not available in previous releases of Oracle Database.

See Also:

Monitoring Compatibility for Apply Processes

This section contains these topics:

Listing Database Objects and Columns Not Compatible with Apply Processes
Listing Columns That Have Become Compatible with Apply Processes Recently

Listing Database Objects and Columns Not Compatible with Apply Processes

A database object or a column in a table is not compatible with apply processes if apply processes cannot apply changes to it. For example, apply processes cannot apply changes to object tables. Apply processes can apply changes to relational tables, but they cannot apply changes to columns of some data types.

The query in this section displays the following information about database objects and columns that are not compatible with apply processes:

The object owner
The object name
The column name
The reason why the column is not compatible with apply processes

To list the columns that are not compatible with apply processes in the local database, run the following query:

COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN APPLY_REASON HEADING 'Apply Process Reason' FORMAT A25
 
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       APPLY_REASON
 FROM DBA_STREAMS_COLUMNS
 WHERE APPLY_VERSION IS NULL;

When a query on the DBA_STREAMS_COLUMNS view returns NULL for APPLY_VERSION, it means that apply processes do not support the column. The WHERE clause in the query ensures that the query only returns columns that are not supported by apply processes.

The following is a sample of the output from this query:

Object
Owner    Object Name          Column Name          Apply Process Reason
-------- -------------------- -------------------- -------------------------
.
.
.
SH       SALES_TRANSACTIONS_E CHANNEL_ID           external table
         XT
OE       ACTION_TABLE         ACTIONED_BY          object table
OE       LINEITEM_TABLE       PART                 object table
PM       ONLINE_MEDIA         PRODUCT_AUDIO        ADT column
OE       CATEGORIES_TAB       CATEGORY_DESCRIPTION object table
.
.
.

To avoid apply errors, configure the apply process rule sets to ensure that the apply process does not try to apply changes to an unsupported database object, such as an object table. To avoid apply errors while applying changes to relational tables, you have the following options:

Configure the apply process rule sets to ensure that the apply process does not try to apply changes to a table that contains one or more unsupported columns.
Configure rule-based transformations to exclude columns that are not supported by apply processes.
Configure procedure DML handlers to exclude columns that are not supported by apply processes.

See Also:

Listing Columns That Have Become Compatible with Apply Processes Recently

The query in this section displays the following information about database objects and columns that have become compatible with apply processes in a recent release of Oracle Database:

The object owner
The object name
The column name
The reason why the object was not compatible with apply processes in previous releases of Oracle Database
The Oracle Database release in which the object became compatible with apply processes

Run the following query to display this information for the local database:

COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A15
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A15
COLUMN APPLY_VERSION HEADING 'Apply|Process|Vesion' FORMAT 99.9
COLUMN APPLY_REASON HEADING 'Apply|Process Reason' FORMAT A25
 
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       APPLY_VERSION,
       APPLY_REASON
 FROM DBA_STREAMS_COLUMNS
 WHERE APPLY_VERSION > 11;

When a query on the DBA_STREAMS_COLUMNS view returns a non-NULL value for APPLY_VERSION, it means that apply processes support the column. The WHERE clause in the query ensures that the query only returns columns that are supported by apply processes. This query returns the columns that have become supported by apply processes in Oracle Database 11g Release 1 and later.

The following is a sample of the output from this query:

Apply
Object                                   Process Apply
Owner    Object Name     Column Name      Vesion Process Reason
-------- --------------- --------------- ------- -------------------------
OE       WAREHOUSES      WAREHOUSE_SPEC     11.1 XMLType column

The Apply Process Version column shows the minimum database compatibility for apply processes to support the column. If the local database compatibility is equal to or higher than the value in the Apply Process Version column for a column, then apply processes can apply changes to the column successfully. You control the compatibility of a database using the COMPATIBLE initialization parameter.

Note:

When this query returns NULL for Apply Process Reason, it means that the column has always been supported by apply processes since the first Oracle Database release that included Oracle Streams.

See Also:

Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
"Listing Database Objects and Columns Not Compatible with Apply Processes"
"Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs" for information about creating rules that use the GET_COMPATIBLE member function for LCRs

Monitoring Oracle Streams Performance Using AWR and Statspack

You can use Automatic Workload Repository (AWR) to monitor performance statistics related to Oracle Streams. If AWR is not available on your database, then you can use the Statspack package to monitor performance statistics related to Oracle Streams. The most current instructions and information about installing and using the Statspack package are contained in the spdoc.txt file installed with your database. Refer to that file for Statspack information. On UNIX systems, the file is located in the ORACLE_HOME/rdbms/admin directory. On Windows systems, the file is located in the ORACLE_HOME\rdbms\admin directory.

See Also:

Oracle Database Performance Tuning Guide for more information about AWR
Chapter 23, "Monitoring the Oracle Streams Topology and Performance" for information about monitoring performance using the Oracle Streams Performance Advisor