A rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. This chapter provides sample queries that you can use to monitor rule-based transformations.
The following topics describe monitoring rule-based transformations:
Note:
The Oracle Streams tool in Oracle Enterprise Manager Cloud Control 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
The query in this section displays the following information about each rule-based transformation in a database:
The owner of the rule for which a rule-based transformation is specified
The name of the rule for which a rule-based transformation is specified
The type of rule-based transformation:
SUBSET RULE is displayed for subset rules, which use internal rule-based transformations.
DECLARATIVE TRANSFORMATION is displayed for declarative rule-based transformations.
CUSTOM TRANSFORMATION is displayed for custom rule-based transformations.
Run the following query to display this information for the rule-based transformations in a database:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN TRANSFORM_TYPE HEADING 'Transformation Type' FORMAT A30
SELECT RULE_OWNER,
RULE_NAME,
TRANSFORM_TYPE
FROM DBA_STREAMS_TRANSFORMATIONS;
Your output looks similar to the following:
Rule Owner Rule Name Transformation Type -------------------- -------------------- ------------------------------ STRMADMIN EMPLOYEES23 DECLARATIVE TRANSFORMATION STRMADMIN JOBS26 DECLARATIVE TRANSFORMATION STRMADMIN DEPARTMENTS33 SUBSET RULE STRMADMIN DEPARTMENTS32 SUBSET RULE STRMADMIN DEPARTMENTS34 SUBSET RULE STRMADMIN DEPARTMENTS32 CUSTOM TRANSFORMATION STRMADMIN DEPARTMENTS33 CUSTOM TRANSFORMATION STRMADMIN DEPARTMENTS34 CUSTOM TRANSFORMATION
A declarative rule-based transformation is a rule-based transformation that covers one of a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL.
The query in this section displays the following information about each declarative rule-based transformation in a database:
The owner of the rule for which a declarative rule-based transformation is specified.
The name of the rule for which a declarative rule-based transformation is specified.
The type of declarative rule-based transformation specified. The following types are possible: ADD COLUMN, DELETE COLUMN, KEEP COLUMNS, RENAME COLUMN, RENAME SCHEMA, and RENAME TABLE.
The precedence of the declarative rule-based transformation. The precedence is the execution order of a transformation in relation to other transformations with the same step number specified for the same rule. For transformations with the same step number, the transformation with the lowest precedence is executed first.
The step number of the declarative rule-based transformation. If more than one declarative rule-based transformation is specified for the same rule, then the transformation with the lowest step number is executed first. You can specify the step number for a declarative rule-based transformation when you create the transformation.
Run the following query to display this information for the declarative rule-based transformations in a database:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN DECLARATIVE_TYPE HEADING 'Declarative|Type' FORMAT A15
COLUMN PRECEDENCE HEADING 'Precedence' FORMAT 99999
COLUMN STEP_NUMBER HEADING 'Step Number' FORMAT 99999
SELECT RULE_OWNER,
RULE_NAME,
DECLARATIVE_TYPE,
PRECEDENCE,
STEP_NUMBER
FROM DBA_STREAMS_TRANSFORMATIONS
WHERE TRANSFORM_TYPE = 'DECLARATIVE TRANSFORMATION';
Your output looks similar to the following:
Declarative
Rule Owner Rule Name Type Precedence Step Number
--------------- --------------- --------------- ---------- -----------
STRMADMIN JOBS26 RENAME TABLE 4 0
STRMADMIN EMPLOYEES23 ADD COLUMN 3 0
Based on this output, the ADD COLUMN transformation executes before the RENAME TABLE transformation because the step number is the same (zero) for both transformations and the ADD COLUMN transformation has the lower precedence.
When you determine which types of declarative rule-based transformations are in a database, you can display more detailed information about each transformation. The following data dictionary views contain detailed information about the various types of declarative rule-based transformations:
The DBA_STREAMS_ADD_COLUMN view contains information about ADD COLUMN declarative transformations.
The DBA_STREAMS_DELETE_COLUMN view contains information about DELETE COLUMN declarative transformations.
The DBA_STREAMS_KEEP_COLUMNS view contains information about KEEP COLUMNS declarative transformations.
The DBA_STREAMS_RENAME_COLUMN view contains information about RENAME COLUMN declarative transformations.
The DBA_STREAMS_RENAME_SCHEMA view contains information about RENAME SCHEMA declarative transformations.
The DBA_STREAMS_RENAME_TABLE view contains information about RENAME TABLE declarative transformations.
For example, the previous query listed an ADD COLUMN transformation and a RENAME TABLE transformation. The following sections contain queries that display detailed information about these transformations:
Note:
Precedence and step number pertain only to declarative rule-based transformations. They do not pertain to subset rule transformations or custom rule-based transformations.See Also:
The following query displays detailed information about the ADD COLUMN declarative rule-based transformations in a database:
COLUMN RULE_OWNER HEADING 'Rule|Owner' FORMAT A9
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN TABLE_NAME HEADING 'Table|Name' FORMAT A9
COLUMN COLUMN_NAME HEADING 'Column|Name' FORMAT A10
COLUMN COLUMN_TYPE HEADING 'Column|Type' FORMAT A8
SELECT RULE_OWNER,
RULE_NAME,
SCHEMA_NAME,
TABLE_NAME,
COLUMN_NAME,
ANYDATA.AccessDate(COLUMN_VALUE) "Value",
COLUMN_TYPE
FROM DBA_STREAMS_ADD_COLUMN;
Your output looks similar to the following:
Rule Rule Schema Table Column Column Owner Name Name Name Name Value Type --------- ------------ ------ --------- ---------- -------------------- -------- STRMADMIN EMPLOYEES23 HR EMPLOYEES BIRTH_DATE SYS.DATE
This output show the following information about the ADD COLUMN declarative rule-based transformation:
It is specified on the employees23 rule in the strmadmin schema.
It adds a column to row LCRs that involve the employees table in the hr schema.
The column name of the added column is birth_date.
The value of the added column is NULL. Notice that the COLUMN_VALUE column in the DBA_STREAMS_ADD_COLUMN view is type ANYDATA. In this example, because the column type is DATE, the ANYDATA.AccessDate member function is used to display the value. Use the appropriate member function to display values of other types.
The type of the added column is DATE.
The following query displays detailed information about the RENAME TABLE declarative rule-based transformations in a database:
COLUMN RULE_OWNER HEADING 'Rule|Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A10
COLUMN FROM_SCHEMA_NAME HEADING 'From|Schema|Name' FORMAT A10
COLUMN TO_SCHEMA_NAME HEADING 'To|Schema|Name' FORMAT A10
COLUMN FROM_TABLE_NAME HEADING 'From|Table|Name' FORMAT A15
COLUMN TO_TABLE_NAME HEADING 'To|Table|Name' FORMAT A15
SELECT RULE_OWNER,
RULE_NAME,
FROM_SCHEMA_NAME,
TO_SCHEMA_NAME,
FROM_TABLE_NAME,
TO_TABLE_NAME
FROM DBA_STREAMS_RENAME_TABLE;
Your output looks similar to the following:
From To From To
Rule Rule Schema Schema Table Table
Owner Name Name Name Name Name
---------- ---------- ---------- ---------- --------------- ---------------
STRMADMIN JOBS26 HR HR JOBS ASSIGNMENTS
This output show the following information about the RENAME TABLE declarative rule-based transformation:
It is specified on the jobs26 rule in the strmadmin schema.
It renames the hr.jobs table in row LCRs to the hr.assignments table.
A custom rule-based transformation is a rule-based transformation that requires a user-defined PL/SQL function. The query in this section displays the following information about each custom rule-based transformation specified in a database:
The owner of the rule on which the custom rule-based transformation is set
The name of the rule on which the custom rule-based transformation is set
The owner and name of the transformation function
Whether the custom rule-based transformation is one-to-one or one-to-many
Run the following query to display this information:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN TRANSFORM_FUNCTION_NAME HEADING 'Transformation Function' FORMAT A30 COLUMN CUSTOM_TYPE HEADING 'Type' FORMAT A11 SELECT RULE_OWNER, RULE_NAME, TRANSFORM_FUNCTION_NAME, CUSTOM_TYPE FROM DBA_STREAMS_TRANSFORM_FUNCTION;
Your output looks similar to the following:
Rule Owner Rule Name Transformation Function Type -------------------- --------------- ------------------------------ ----------- STRMADMIN DEPARTMENTS31 "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE STRMADMIN DEPARTMENTS32 "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE STRMADMIN DEPARTMENTS33 "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE
Note:
The transformation function name must be of typeVARCHAR2. If it is not, then the value of TRANSFORM_FUNCTION_NAME is NULL. The VALUE_TYPE column in the DBA_STREAMS_TRANSFORM_FUNCTION view displays the type of the transform function name.