REMOVE_STREAMS_CONFIGURATION Drops Rules Configured Using DBMS_RULE_ADM (Doc ID 751357.1)

Last updated on MAY 15, 2023

Applies to:

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

PROBLEM

As per 10g and 11g documents:-

Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1.)
Part Number B28419-02

REMOVE_STREAMS_CONFIGURATION drops rules that were created using the DBMS_STREAMS_ADM package,but does not drop rules that were created using the DBMS_RULE_ADM package.

But above restriction is not TRUE .

In the example below you can see that REMOVE_STREAMS_CONFIGURATION drops
rules which were created using DBMS_RULE_ADM and used in the current Streams configuration.

### Steps to Reproduce ###

 -- create a ruleset/rule and assign it to a capture process

BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'strmadmin.test_ruleset',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
END;
/

BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.test_role',
condition => ':ddl.get_object_owner() IN (''SCHEMA1'',
''SCHEMA2'')');
END;
/

BEGIN
DBMS_RULE_ADM.ADD_RULE(
rule_name => 'strmadmin.test_role',
rule_set_name => 'strmadmin.test_ruleset');
END;
/

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'capture_queue_01',
capture_name => 'capture_test',
rule_set_name => 'strmadmin.test_ruleset',
source_database => 'ora11g_1.siemens.ch',
negative_rule_set_name => NULL,
capture_user => 'strmadmin');
END;
/
-- create a ruleset/rule but DON'T assign it to any stream yet

BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'strmadmin.test2_ruleset',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
END;
/

BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.test2_role',
condition => ':ddl.get_object_owner() IN (''SCHEMA1'',
''SCHEMA2'')');
END;
/

BEGIN
DBMS_RULE_ADM.ADD_RULE(
rule_name => 'strmadmin.test2_role',
rule_set_name => 'strmadmin.test2_ruleset');
END;
/

SQL> select rule_owner,rule_name from all_rules where rule_name like 'TEST%';

RULE_OWNER RULE_NAME
------------------------------ ------------------------------
STRMADMIN TEST2_ROLE
STRMADMIN TEST_ROLE

SQL> select owner,ruleset_name from all_rulesets where ruleset_name like 'TEST%';

OWNER RULESET_NAME
------------------------------ ------------------------------
STRMADMIN TEST2_RULESET
STRMADMIN TEST_RULESET

SQL> select capture_name, queue_name from all_capture where capture_name = 'CAPTURE_TEST';

CAPTURE_NAME QUEUE_NAME
------------------------------ ------------------------------
CAPTURE_TEST CAPTURE_QUEUE_01

-- remove the streams

exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

SQL> select rule_owner,rule_name from all_rules where rule_name like 'TEST%';

RULE_OWNER RULE_NAME
------------------------------ ------------------------------
STRMADMIN TEST2_ROLE

SQL> select owner,ruleset_name from all_rulesets where ruleset_name like 'TEST%';

OWNER RULESET_NAME
------------------------------ ------------------------------
STRMADMIN TEST2_RULESET

SQL> select capture_name, queue_name from all_capture where capture_name = 'CAPTURE_TEST';

no rows selected

The TEST ruleset/rules have been removed as well!!

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.