Re: enable query rewrite fails

On Wed, 24 Aug 2005 17:17:43 +0200, Christian Bang <christian.bang_at_REMOVEMEbeautec.de> wrote:

>sybrandb_at_yahoo.com wrote:
>> ora-4039 is usually thrown when you don't have the feature installed.
>> As you have EE, it should work for you.
>> What does v$option tell you?
>> You might want to check, using the installer, whether you have the Data
>> Mining Option installed.
>>
>
>I have Data Mining installed (shown in installer), as well as OLAP.
>The corresponding directories are there as well.
>But as you can see below, the v$option returns FALSE. (I also restarted
>the db.)
>Does one really need Data Mining for QUERY REWRITE? I don't think so...
>
>
>PARAMETER VALUE
>
>----------------------------------------------------------------
>----------------------------------------------------------------
>Partitioning FALSE
>
>Objects TRUE
>
>Real Application Clusters FALSE
>
>Advanced replication FALSE
>
>Bit-mapped indexes FALSE
>
>Connection multiplexing TRUE
>
>Connection pooling TRUE
>
>Database queuing TRUE
>
>Incremental backup and recovery FALSE
>
>Instead-of triggers TRUE
>
>Parallel backup and recovery FALSE
>
>Parallel execution FALSE
>
>Parallel load TRUE
>
>Point-in-time tablespace recovery FALSE
>
>Fine-grained access control FALSE
>
>Proxy authentication/authorization TRUE
>
>Change Data Capture FALSE
>
>Plan Stability TRUE
>
>Online Index Build FALSE
>
>Coalesce Index FALSE
>
>Managed Standby FALSE
>
>Materialized view rewrite FALSE
>
>Materialized view warehouse refresh FALSE
>
>Database resource manager FALSE
>
>Spatial FALSE
>
>Visual Information Retrieval FALSE
>
>Export transportable tablespaces FALSE
>
>Transparent Application Failover FALSE
>
>Fast-Start Fault Recovery FALSE
>
>Sample Scan TRUE
>
>Duplexed backups FALSE
>
>Java TRUE
>
>OLAP Window Functions TRUE
>
>Block Media Recovery FALSE
>
>Fine-grained Auditing FALSE
>
>Application Role FALSE
>
>Enterprise User Security FALSE
>
>Oracle Data Guard FALSE
>
>Oracle Label Security FALSE
>
>OLAP FALSE
>
>Heap segment compression FALSE
>
>Join index FALSE
>
>Trial Recovery FALSE
>
>Oracle Data Mining FALSE
>
>Online Redefinition FALSE
>
>Streams FALSE
>
>Very Large Memory FALSE
>
>File Mapping FALSE

Right now you have so many EE options disabled, that one is looking at a complete mess, and you can expect catproc.sql never ran successfully.
>Materialized view rewrite FALSE
>
>Materialized view warehouse refresh FALSE
should be true, and there are numerous others.

I would rerun
?/rdbms/admin/catalog..sql
?/rdbms/admin/catproc.sql
connected as SYS as SYSDBA
making sure you issue a spool command prior to starting those two scripts, and see what happens.
Several of the options above are implemented by pl/sql packages (FGAC by the dbms_rls package, Materialized Views by the dbms_mview package), a simple describe <package name> in sqlplus would confirm you simply don't have them, and they need to be installed. Catproc does that for you, and it requires catalog.sql to be run prior to running catproc.

--
Sybrand Bakker, Senior Oracle DBA