Oracle Rules Manager 和 Oracle Expression Filter 組件 說明

 

一.Rules Manager 和 Expression Filter 組件說明

 

在說明之前，我們先用如下SQL查看一下DB中的組件：

SQL> col comp_id for a15

SQL> col version for a15

SQL> col comp_name for a30

SQL> select comp_id,comp_name,versionfrom dba_registry ;

 

COMP_ID         COMP_NAME                      VERSION

--------------------------------------------- ---------------

XDB             Oracle XML Database            11.2.0.3.0

AMD             OLAP Catalog                   11.2.0.3.0

EM              Oracle Enterprise Manager      11.2.0.3.0

SDO             Spatial                        11.2.0.3.0

ORDIM           Oracle Multimedia              11.2.0.3.0

CONTEXT         Oracle Text                    11.2.0.3.0

ODM             Oracle Data Mining             11.2.0.3.0

EXF             Oracle Expression Filter       11.2.0.3.0

RUL             Oracle Rules Manager           11.2.0.3.0

OWM             Oracle Workspace Manager       11.2.0.3.0

CATALOG         Oracle Database Catalog Views  11.2.0.3.0

 

COMP_ID         COMP_NAME                      VERSION

--------------------------------------------- ---------------

CATPROC         Oracle Database Packages and T11.2.0.3.0

                ypes

 

JAVAVM          JServer JAVA Virtual Machine   11.2.0.3.0

XML             Oracle XDK                     11.2.0.3.0

CATJAVA         Oracle Database Java Packages  11.2.0.3.0

XOQ             Oracle OLAP API                11.2.0.3.0

APS             OLAP Analytic Workspace        11.2.0.3.0

 

17 rows selected.

 

MOS：

Oracle 8i/9i/10g/11g 組件（Components） 說明

http://blog.csdn.net/tianlesoftware/article/details/5937382

 

The 10i newfeature allows you to store conditional expressions as data in the database.The Expression Filter provides a mechanism that you use to place a constrainton a VARCHAR2 column to ensure that the values stored are valid SQL WHEREclause expressions. The mechanism also identifies the set of attributes thatcan be referenced in the conditional expressions. 

--Oracle 10g的一個新特性就是允許我們在資料庫中用data的方式儲存條件運算式。 Expression Filter 組件提供了機制，通過這個機制，我們可以在Varchar2類型的列上添加約束，來確儲存儲有效SQL Where 條件運算式。這個機制也可以標記一個屬性的集合，其可以用來在條件運算式中參考。

 

OracleExpression Filter, a feature of Oracle Database 10g, is a component of RulesManager that allows application developers to store, index, and evaluateconditional expressions (expressions) in one or more columns of a relationaltable. Expressions are a useful way to describe interests in expected data. 

--Expression Filter 是Rules Manager 的一個組件。其允許研發人員store,index 和 evaluate 條件運算式在相關表的一個或多個列上。

 

ExpressionFilter matches incoming data with expressions stored in a column to identifyrows of interest. It can also derive complex relationships by matching data inone table with expressions in a second table. Expression Filter simplifies SQLqueries; allows expressions to be inserted, updated, and deleted withoutchanging the application; and enables reuse of conditional expressions in rulesby separating them from the application and storing them in the database.Applications involving information distribution, demand analysis, and taskassignment can benefit from Expression Filter. 

--Expression Filter 使用存在列裡的運算式來匹配輸入的資料。 ExpressionFilter 簡化了SQL 查詢，允許運算式進行insert，update和delete 操作而不用改變應用程式。

 

The RulesManager and Expression Filter features are the same in the Standard andEnterprise Editions. Support for indexing expressions is available only in theEnterprise Edition because it requires bitmap index support. 

 

組件使用者：

EXFSYS: 

Each is suppliedas a set of PL/SQL packages, a Java package, a set of dictionary tables, andcatalog views. All these objects are created in a dedicated schema namedEXFSYS. 

 

官網說明：

OracleExpression Filter Concepts 10gR2

 

 

二．組件重建

MOS:

Note 258618.1 - How To Install andUninstall Expression Filter Feature or EXFSYS schema

 

官網：

Installing Rules Managerand Expression Filter 11gR2

http://docs.oracle.com/cd/E11882_01/appdev.112/e14919/exprn_app_e.htm

 

 

2.1 Installing the EXFSYS schema: 

 

1. Login as SYSDBA 

2. @?/rdbms/admin/catexf.sql 

注意：

1. Installing Expression Filter does notautomatically install Rules Manager. 
    This component has to be installed separately using thecatrul.sql script

--安裝Expression Filter 不會自動安裝Rules Manager。所有RulesManager 需要使用catrul.sql 獨立安裝。

2. When reinstalling Expression Filter, you may have to run utlrp.sql torecompile some invalid objects.

       --在卸載Expression Filter 時，需要使用urlrp.sql 來編譯一些無效對象。

 

During installation of OracleDatabase, a demonstration script is installed for both the Rules Manager and Expression Filter features. The scripts ruldemo.sql (Rules Manager demo) andexfdemo.sql (Expression Filter demo) are located in the$ORACLE_HOME/rdbms/demo/ directory. 

 

 

2.2 Uninstalling the EXFSYS schema: 

1. Login as SYSDBA 

2. @?/rdbms/admin/catnoexf.sql 

注意：

1. Uninstalling Expression Filter implicitly uninstalls Rules Manager.

       --卸載Expression Filter 組件時會自動卸載Rules Manager 組件。

 

2. The CATNOEXF.SQL script may not droppublic synonyms created by catexf.sql.
    However, with 11.2, it was verified no PUBLIC SYNONYMS fortable_owner = 'EXFSYS' were left.

       --catnoexf.sql 指令碼不會刪除由catexf.sql 產生的公用同義字。但是從Oracle 11gR2 開始，它會驗證是否有public sysnonyms的存在。

 

The followingscript can be used to drop all PUBLIC SYNONYMS created for Rules Manager andExpression Filter when needed:

       --如下指令碼可以刪除所有EXFSYS 使用者的PUBLIC SYNONYMS：

 

declare 
  cursor cur1 is select synonym_name from all_synonyms where owner ='PUBLIC' and table_owner = 'EXFSYS'; 
begin 
  for c1 in cur1 loop 
    EXECUTE IMMEDIATE 'drop public synonym ' ||dbms_assert.enquote_name(c1.synonym_name, false); 
  end loop; 
end; 
/

 

In someversions before 10.2, or if you do not have the dbms_assert package, thefollowing script can be used instead: 

--在Oracle 10gR2 之前，如果沒有dbms_assert 包，那麼可以使用如下的指令碼來刪除PUBLIC SYNONYMS：

 

declare
  cursor cur1 is select synonym_name from all_synonyms where owner ='PUBLIC' and table_owner = 'EXFSYS';
begin
  for c1 in cur1 loop
    EXECUTE IMMEDIATE 'drop public synonym"'||c1.synonym_name||'"';
  end loop;
end;
/ 

 

Thefollowing is a list of the public synonyms that may remain after justrunning CATNOEXF.SQL and these would be dropped by running one of the abovescripts:

--catanoexf.sql 不能刪除的public synonyms列表如下：

EXF$INDEXOPER 
EXF$ATTRIBUTE 
EXF$ATTRIBUTE_LIST 
EXF$TABLE_ALIAS 
EXF$XPATH_TAG 
EXF$XPATH_TAGS 
EVALUATE 
DBMS_EXPFIL 
USER_EXPFIL_ATTRIBUTE_SETS 
ALL_EXPFIL_ATTRIBUTE_SETS 
USER_EXPFIL_ATTRIBUTES 
ALL_EXPFIL_ATTRIBUTES 
USER_EXPFIL_DEF_INDEX_PARAMS 
ALL_EXPFIL_DEF_INDEX_PARAMS 
USER_EXPFIL_INDEX_PARAMS 
ALL_EXPFIL_INDEX_PARAMS 
USER_EXPFIL_ASET_FUNCTIONS 
ALL_EXPFIL_ASET_FUNCTIONS 
USER_EXPFIL_INDEXES 
ALL_EXPFIL_INDEXES 
USER_EXPFIL_PREDTAB_ATTRIBUTES 
ALL_EXPFIL_PREDTAB_ATTRIBUTES 
USER_EXPFIL_EXPRESSION_SETS 
ALL_EXPFIL_EXPRESSION_SETS 
USER_EXPFIL_PRIVILEGES 
USER_EXPFIL_EXPRSET_STATS
ALL_EXPFIL_EXPRSET_STATS
RLM$EVENTIDS 
RLM$TABLE_ALIAS 
DBMS_RLMGR 
USER_RLMGR_EVENT_STRUCTS 
ALL_RLMGR_EVENT_STRUCTS 
USER_RLMGR_RULE_CLASSES 
ALL_RLMGR_RULE_CLASSES 
USER_RLMGR_RULE_CLASS_STATUS 
ALL_RLMGR_RULE_CLASS_STATUS 
USER_RLMGR_PRIVILEGES 
ADM_RLMGR_PRIVILEGES 
USER_RLMGR_COMPRCLS_PROPERTIES 
ALL_RLMGR_COMPRCLS_PROPERTIES 
USER_RLM4J_EVENT_STRUCTS
USER_RLM4J_RULE_CLASSES

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Skype: tianlesoftware

Email:   tianlesoftware@gmail.com

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940