29 Monitoring Other Oracle Streams Components

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
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
ORDDATA  ORDDCM_PREFS_DEF_VALUES_TAB    column with user-defined type  NO
ORDDATA  ORDDCM_PREFS_LOOKUP            column with user-defined type  NO
ORDDATA  ORDDCM_PREFS_VALID_VALUES_TAB  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.

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
ORDDATA    ORDDCM_CT_PRED_OPRD  table with XMLType column      11.1
ORDDATA    ORDDCM_CT_PRED_OPRD_ table with XMLType column      11.1
           WRK
ORDDATA    ORDDCM_DOCS          table with XMLType column      11.1
ORDDATA    ORDDCM_DOCS_WRK      table with XMLType column      11.1
ORDDATA    ORDDCM_MAPPING_DOCS  table with XMLType column      11.1
ORDDATA    ORDDCM_MAPPING_DOCS_ table with XMLType column      11.1
           WRK
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.

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
-------- -------------------- -------------------- -------------------------
.
.
.
PM       TEXTDOCS_NESTEDTAB   FORMATTED_DOC        object table
IX       ORDERS_QUEUETABLE    Q_NAME               AQ queue table
IX       ORDERS_QUEUETABLE    PRIORITY             AQ queue table
IX       ORDERS_QUEUETABLE    DELAY                AQ queue table
IX       ORDERS_QUEUETABLE    DSCN                 AQ queue table
IX       AQ$_ORDERS_QUEUETABL SUBSCRIBER_ID        AQ queue table
         E_S
OE       CUSTOMERS            CUST_GEO_LOCATION    ADT column
OE       CATEGORIES_TAB       CATEGORY_NAME        object table
SH       DR$SUP_TEXT_IDX$I    TOKEN_TYPE           domain index
SH       DR$SUP_TEXT_IDX$I    TOKEN_FIRST          domain index
.
.
.

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.

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
ORDDATA  ORDDCM_CT_PRED_ OPERAND            11.1 XMLType column
         OPRD_WRK
ORDDATA  ORDDCM_MAPPING_ XSLT               11.1 XMLType column
         DOCS_WRK
ORDDATA  ORDDCM_DOCS     DOC_CONTENT        11.1 XMLType column
ORDDATA  ORDDCM_DOCS_WRK DOC_CONTENT        11.1 XMLType column
OE       WAREHOUSES      WAREHOUSE_SPEC     11.1 XMLType column
ORDDATA  ORDDCM_MAPPING_ XSLT               11.1 XMLType column
         DOCS
ORDDATA  ORDDCM_CT_PRED_ OPERAND            11.1 XMLType column
         OPRD

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.