Oracle英文版培训课件之Implement Streams：les04

System-CreatedRulesObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Identifythedifferenttypesofsystem-createdrulesUseanegativerulesettopreventaStreamsclientfromperformingitstaskonanindividualobjectUsesubsetrulestorestrictthedatathatflowsthroughthestreamCustomizesystem-createdrulesQuerythedatadictionaryforinformationabout

system-createdrulesUsingRulesinOracleStreamsThefollowingStreamsclientsuserulesandrulesets:CaptureprocessPropagationjobApplyprocessMessagingclientThesamerulesetcanbeusedbymultipleStreamsclientsinthesamedatabase.MultiplerulesinarulesetarecombinedwiththeORoperator.UsingRulesinOracleStreamsYoucanuserulestodefinethegranularityatwhichacaptureprocess,propagationjob,orapplyprocessreplicatesdata.Levelsofgranularity:DatabaseSchemaTableSubsetofrowsinatableDMLoperationsonlyDDLoperationsonlySubsetofoperationsthatareperformedagainstatableGeneratingSystem-CreatedRulesYouuseproceduresintheDBMS_STREAMS_ADMpackagetogeneratesystem-createdrules.Foraspecificcomponent:ADD_TABLE_RULESADD_SCHEMA_RULESADD_GLOBAL_RULESADD_SUBSET_RULES

ADD_*_PROPAGATION_RULESFormultiplecomponents:MAINTAIN_SIMPLE_TTS,MAINTAIN_TTSMAINTAIN_GLOBALMAINTAIN_SCHEMASMAINTAIN_TABLESGeneratingSystem-CreatedRulesTheproceduresintheDBMS_STREAMS_ADMpackagethatgeneratesystem-createdrulesalsoperformthefollowingactions:Createacaptureprocess,propagationjob,orapplyprocessifthespecifiedprocessorjobdoesnotalreadyexistCreatearulesetforthespecifiedcaptureprocess,propagationjob,orapplyprocessifarulesetdoesnotalreadyexistfortheprocessorjobCreatezeroormorerulesandaddtherulestotherulesetfortheprocessorjobSystem-CreatedRule:ExampleSystem-createdruleconditionforthetable-levelrule:EXECUTEDBMS_STREAMS_ADM.ADD_TABLE_RULES(-table_name=>'hr.employees',-streams_type=>'apply',-streams_name=>'apply_site1_lcrs',-queue_name=>'streams_queue',-source_database=>'');:dml.get_object_owner()='HR'and:dml.get_object_name()='EMPLOYEES'AND:dml.is_null_tag()='Y'AND:dml.get_source_database_name()='SITE1.NET'System-CreatedRuleComponentsUseabuilt-inevaluationcontextMayhaveoneormoreassociatedactioncontextsCapture

rulesetEvaluationcontextEvaluationcontextActioncontextTableruleEvaluationcontextSchemaruleUsingSubsetRuleswithOracleStreamsSubsetrulescanbecreatedforcapture,propagation,andapplywiththeDBMS_STREAMS_ADMpackage.Site1CaptureSite2PropApplyCreatingSubsetRulesBEGINDBMS_STREAMS_ADM.ADD_SUBSET_RULES(table_name=>'hr.regions',dml_condition=>'region_id=2',streams_type=>'capture',streams_name=>'hr_capture', queue_name=>'hr_queue');END;/RowSubsettingdml_condition=>'order_status<5'online04-JUN-0423890direct05-MAY-0424567direct14-DEC-04245194ORDER_STATUSonline18-NOV-042390ORDER_MODEORDER_DATEORDER_ID0direct04-OCT-0424535direct 31-OCT-042457CustomizingSystem-CreatedRules(:ddl.get_object_owner()='HR')and(:ddl.get_object_type()!='INDEX')BEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES(schema_name=>'hr',streams_type=>'apply',streams_name=>'apply_site1_lcrs',queue_name=>'strmadmin.streams_queue',include_dml=>FALSE,include_ddl=>TRUE,and_condition=>':lcr.get_object_type()!=''INDEX'''

);END;/NegativeRuleSetsCapturePositive

rulesetNegative

rulesetRuleEvaluationwithRuleSetsPositive

rulesetNegative

ruleset1Positive

rulesetNegative

ruleset234TRUETRUEFALSEFALSECaptureruleset_posHRschemaHR.JOB_HISTORYruleset_negNegativeRuleSets:ExampleCreatingNegativeRulesBEGINDBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name=>'hr.locations',streams_type=>'capture',streams_name=>'capture1',queue_name=>'strmadmin.streams_queue',inclusion_rule=>FALSE);END;/MonitoringSystem-CreatedRulesStreamsclientrules:[ALL|DBA]_STREAMS_RULES[ALL|DBA]_STREAMS_GLOBAL_RULES[ALL|DBA]_STREAMS_SCHEMA_RULES[ALL|DBA]_STREAMS_TABLE_RULESStreamsclientrulesets:[ALL|DBA]_CAPTURE[ALL|DBA]_PROPAGATION[ALL|DBA]_APPLYMessagerulesandrulesets:[ALL|DBA]_STREAMS_MESSAGE_RULES[ALL|DBA]_STREAMS_MESSAGE_CONSUMERSMonitoringNegativeRuleSetsPROPAGATION_NAME--------------------------------------NEGATIVE_RULE_SET_OWNER---------------------------NEGATIVE_RULE_SET_NAME----------------------------------------PROP_TO_SITE2STRMADMINRULESET$_31SQL>SELECTPROPAGATION_NAME,

2>NEGATIVE_RULE_SET_OWNER,

3>NEGATIVE_RULE_SET_NAME4>FROMDBA_PROPAGATION;SummaryInthislesson,youshouldhavelearnedhowto:Identifythedifferenttypesofsystem-createdrulesUseanegativerulesettopreventaStreamsclientfromperformingitstaskonanindividualobjectUsesubsetrulestorestrictthedatathatflowsthroughthestreamCustomizesystem-createdrul