ORACLE里居然有这样的事？

最新推荐文章于 2022-10-20 11:52:22 发布

原创最新推荐文章于 2022-10-20 11:52:22 发布 · 724 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #object #null #sql #table #action

本文记录了一次使用Oracle数据库中DBMS_STREAMS_ADM包的REMOVE_RULE过程来删除规则的具体操作经历。通过对比不同大小写的规则名称，展示了如何正确地指定要删除的规则，最终成功执行了删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

提供了一个删除RULE的过程居然不好用了。

结果发现是大小写的事？

SQL> BEGIN
2    DBMS_STREAMS_ADM.REMOVE_RULE(
3    rule_name   =>'CMDB1',
4    streams_type =>'CAPTURE',
5    streams_name   =>'STREAMS_CAPTURE'
);
6    7    end;
8    /
BEGIN
*
ERROR at line 1:
ORA-24147: rule STRMADMIN.CMDB1 does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 559
ORA-06512: at line 2

SQL> BEGIN
2    DBMS_STREAMS_ADM.REMOVE_RULE(
3    rule_name   =>'CMDB',
4    streams_type =>'CAPTURE',
5    streams_name   =>'STREAMS_CAPTURE'
6    );
7    end;
8    /
BEGIN
*
ERROR at line 1:
ORA-24147: rule STRMADMIN.CMDB does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 559
ORA-06512: at line 2
SQL> BEGIN
2     DBMS_STREAMS_ADM.REMOVE_RULE(
3     rule_name   =>'&rule_name',
4     streams_type =>'CAPTURE',
5     streams_name   =>'STREAMS_CAPTURE'
6     );
7 END;
8 /
Enter value for rule_name: cmdb1
old   3:    rule_name   =>'&rule_name',
new   3:    rule_name   =>'cmdb1',
BEGIN
*
ERROR at line 1:
ORA-24147: rule SYS.CMDB1 does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 559
ORA-06512: at line 2

SQL> desc dba_rules
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
RULE_OWNER                                NOT NULL VARCHAR2(30)
RULE_NAME                                 NOT NULL VARCHAR2(30)
RULE_CONDITION                                     CLOB
RULE_EVALUATION_CONTEXT_OWNER                      VARCHAR2(30)
RULE_EVALUATION_CONTEXT_NAME                       VARCHAR2(30)
RULE_ACTION_CONTEXT                                SYS.RE$NV_LIST
RULE_COMMENT                                       VARCHAR2(4000)

SQL> select RULE_NAME,rule_owner,rule_condition from dba_rules;

RULE_NAME                      RULE_OWNER
------------------------------ ------------------------------
RULE_CONDITION
--------------------------------------------------------------------------------
ALERT_QUE$1                    SYS
tab.user_data.MESSAGE_LEVEL = 5 AND tab.user_data.MESSAGE_GROUP = 'High Availabi

cmdb1                          STRMADMIN
((:dml.get_object_owner() = 'cmdb') and :dml.is_null_tag() = 'Y' )

cmdb2                          STRMADMIN
((:ddl.get_object_owner() = 'cmdb' or :ddl.get_base_table_owner() = 'cmdb') and

RULE_NAME                      RULE_OWNER
------------------------------ ------------------------------
RULE_CONDITION
--------------------------------------------------------------------------------
cmdb4                          STRMADMIN
((:dml.get_object_owner() = 'cmdb') and :dml.is_null_tag() = 'Y' and :dml.get_so

cmdb5                          STRMADMIN
((:ddl.get_object_owner() = 'cmdb' or :ddl.get_base_table_owner() = 'cmdb') and

SQL> set head off
SQL> /

ALERT_QUE$1                    SYS
tab.user_data.MESSAGE_LEVEL = 5 AND tab.user_data.MESSAGE_GROUP = 'High Availabi

cmdb1                          STRMADMIN
((:dml.get_object_owner() = 'cmdb') and :dml.is_null_tag() = 'Y' )

cmdb2                          STRMADMIN
((:ddl.get_object_owner() = 'cmdb' or :ddl.get_base_table_owner() = 'cmdb') and

cmdb4                          STRMADMIN
((:dml.get_object_owner() = 'cmdb') and :dml.is_null_tag() = 'Y' and :dml.get_so

cmdb5                          STRMADMIN

((:ddl.get_object_owner() = 'cmdb' or :ddl.get_base_table_owner() = 'cmdb') and

SQL>
SQL> BEGIN
2    DBMS_STREAMS_ADM.REMOVE_RULE(
3    rule_name   =>'"cmdb1"',
4    streams_type =>'CAPTURE',
5    streams_name   =>'STREAMS_CAPTURE'
6    );
7    end;
8    /

PL/SQL procedure successfully completed.

SQL>