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.
This chapter contains these topics:
Note:
The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.See Also:
Oracle Database Reference for information about the data dictionary views described in this chapter
Oracle Streams Replication Administrator's Guide for information about monitoring a Streams replication environment
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
, 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_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.