Re: enable query rewrite fails

DA Morgan wrote:

>> 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 agree with you, they should be TRUE.

>> 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.

I ran these files without any errors in the spool output. The v$option still tells the same.
a "describe dbms_mview" works, the packages are there.

> And after following Sybrand's advice do the following:
> 
> SELECT owner, COUNT(*)
> FROM dba_objects
> WHERE status = 'INVALID';
> 
> If anything is invalid run $ORACLE_HOME/rdbms/admin/utlrp.sql

There are no invalid packages of the owner SYS (only some own packages).

It seems to be quite a mess with the installation. I installed the 9.2 server on another machine and there it works, as well on a 10 server. The code executes and v$option has the needed TRUE's.

I'd like to keep this installation because it contains all the data... Any idea what else could be broken in this installation?

Christian Received on Thu Aug 25 2005 - 03:41:02 CDT