How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)

  • Home
  • Oracle
  • How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)

Download PDF

How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2016***

GOAL

Expression Filter (and the related Rules Manager) are installed automatically with Oracle Database 10g Standard Edition and Oracle Database 10g Enterprise Edition and higher. It is supplied as a set of PL/SQL packages, a Java package, a set of dictionary tables, and catalog views. All these objects are created in a dedicated schema named EXFSYS.

Please refer to
Oracle® Database Rules Manager and Expression Filter Developer's Guide
11g Release 2 (11.2)
Appendix: E Installing Rules Manager and Expression Filter
http://docs.oracle.com/cd/E11882_01/appdev.112/e14919/exprn_app_e.htm#EXPRN113


The Expression Filter and related Rules Manager can be seen as installed components in DBA_REGISTRY:

SQL> column comp_id format a16
SQL> column comp_name format a36
SQL> column version format a12
SQL> column status format a7
SQL> select comp_id, comp_name, version, status from dba_registry;

COMP_ID          COMP_NAME                            VERSION      STATUS
---------------- ------------------------------------ ------------ -------
...
EXF              Oracle Expression Filter             11.2.0.1.0   VALID
RUL              Oracle Rules Manager                 11.2.0.1.0   VALID
...

18 rows selected.

SQL>

SOLUTION

Installing the EXFSYS schema:

1. Login as SYSDBA

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

NOTE:
1. Installing Expression Filter does not automatically install Rules Manager.
    This component has to be installed separately using the catrul.sql script

2. When reinstalling Expression Filter, you may have to run utlrp.sql to recompile some invalid objects. if this does not help, then to validate the components follow the Note 457861.1

If you dont see (or not found or missing) the Oracle Expression Filter (EXF) or Oracle Rules Manager (RUL) in the dba_registry, then you have to repeat the above steps after running the below scripts, (Refer Note 753041.1)

Restart database with "startup upgrade"

SQL> @?/rdbms/admin/exfpatch.sql

SQL> @?/rdbms/admin/rulpatch.sql

Uninstalling the EXFSYS schema:

1. Login as SYSDBA

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

NOTE:
1. Uninstalling Expression Filter implicitly uninstalls Rules Manager.

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



The following script can be used to drop all PUBLIC SYNONYMS created for Rules Manager and Expression Filter when needed:

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 some versions before 10.2, or if you do not have the dbms_assert package, the following script can be used instead:

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;
/



The following is a list of the public synonyms that may remain after just running CATNOEXF.SQL and these would be dropped by running one of the above scripts:

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

For 12c:

Starting with Oracle Database 12c, the Expression Filter (EXF) and Database Rules Manager (RUL) features are desupported. If you are using Rules Manager, Oracle recommends that you consider migrating to Oracle Business Rules, a component of Oracle Fusion Middleware. The Continuous Query Notification feature of Oracle Database replaces Expression Filter.


Please review:Obsolescence Notice: Rules Manager and Expression Filter Features of Oracle Database (Doc ID 1244535.1)