Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

B28321-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

28 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:

Note:

The Oracle Streams tool in the Oracle Enterprise Manager Console 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:

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:

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 Oracle Streams Administrator' FORMAT A30

SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR
  WHERE LOCAL_PRIVILEGES = 'YES';

Your output looks similar to the following:

Local Oracle 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.

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:

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:

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

This section contains these topics:

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_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_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
OE       CUSTOMERS                      column with user-defined type  NO
OE       LINEITEM_TABLE                 column with user-defined type  NO
OE       PRODUCT_REF_LIST_NESTEDTAB     column with user-defined type  NO
OE       SUBCATEGORY_REF_LIST_NESTEDTAB column with user-defined type  NO
PM       ONLINE_MEDIA                   column with user-defined type  NO
PM       PRINT_MEDIA                    column with user-defined type  NO
PM       TEXTDOCS_NESTEDTAB             column with user-defined type  NO
SH       DIMENSION_EXCEPTIONS           unsupported column exists      NO
SH       DR$SUP_TEXT_IDX$I              domain index                   YES
SH       DR$SUP_TEXT_IDX$K              unsupported column exists      YES
SH       DR$SUP_TEXT_IDX$N              domain index                   YES
SH       DR$SUP_TEXT_IDX$R              domain index                   YES
SH       SALES_TRANSACTIONS_EXT         external table                 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:

Listing the Database Objects That Have Become Compatible With Capture Processes Recently

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:

Listing Database Objects and Columns That Are 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
-------- -------------------- -------------------- -------------------------
OE       CATEGORIES_TAB       CATEGORY_ID          object table
PM       ONLINE_MEDIA         PRODUCT_TEXT         (N)CLOB column
IX       ORDERS_QUEUETABLE    EXCEPTION_QSCHEMA    AQ queue table
IX       ORDERS_QUEUETABLE    EXCEPTION_QUEUE      AQ queue table
OE       WAREHOUSES           WH_GEO_LOCATION      ADT column
OE       ACTION_TABLE         DATE_ACTIONED        object table
OE       LINEITEM_TABLE       DESCRIPTION          object table
PM       ONLINE_MEDIA         PRODUCT_THUMBNAIL    ADT column
PM       PRINT_MEDIA          AD_FLTEXTN           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.

Monitoring Compatibility for Apply Processes

This section contains these topics:

Listing Database Objects and Columns That Are 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
-------- -------------------- -------------------- -------------------------
OE       CATEGORIES_TAB       CATEGORY_ID          object table
IX       ORDERS_QUEUETABLE    EXCEPTION_QSCHEMA    AQ queue table
IX       ORDERS_QUEUETABLE    EXCEPTION_QUEUE      AQ queue table
OE       WAREHOUSES           WH_GEO_LOCATION      ADT column
OE       ACTION_TABLE         DATE_ACTIONED        object table
OE       LINEITEM_TABLE       DESCRIPTION          object table
PM       ONLINE_MEDIA         PRODUCT_THUMBNAIL    ADT column
.
.
.

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 DML handlers to exclude columns that are not supported by apply processes.

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 > 10;

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 10g 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
-------- --------------- --------------- ------- -------------------------
SH       CAL_MONTH_SALES CALENDAR_MONTH_    10.1 materialized view
         _MV             DESC
PM       PRINT_MEDIA     AD_FLTEXTN         10.1 NCLOB column
HR       COUNTRIES       COUNTRY_ID         10.1 IOT
SH       FWEEK_PSCAT_SAL WEEK_ENDING_DAY    10.1 materialized view
         ES_MV
SH       FWEEK_PSCAT_SAL DOLLARS            10.1 materialized view
         ES_MV
HR       COUNTRIES       COUNTRY_NAME       10.1 IOT
HR       COUNTRIES       REGION_ID          10.1 IOT
SH       CAL_MONTH_SALES DOLLARS            10.1 materialized view
         _MV
SH       FWEEK_PSCAT_SAL PROD_SUBCATEGOR    10.1 materialized view
         ES_MV           Y
SH       FWEEK_PSCAT_SAL CHANNEL_ID         10.1 materialized view
         ES_MV
SH       FWEEK_PSCAT_SAL PROMO_ID           10.1 materialized view
         ES_MV
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.

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:

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:

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: