Undefined Materialized View

Version : Oracle 8.1.7.4
OS: Solaris 8
Architecture: Star Schema Data Mart with Materialized View with underlying partitioned fact table and aggregate table.

As stated in several Oracle reference books, our Materialized View becomes "UNDEFINED" or "Unusable" each time DML is used against it for refreshing it. This is expected and is resolved by "ALTER MATERIALIZED VIEW <viewname> compile;" and works fine manually.

It has begun to cause a problem when introduced in our automated load process and locks the underlying tables (the two facts) during the ALTER MV statement. The problem is that it hangs the tables during this lock transaction and never releases them in turn, hanging the database for any additional processes waiting to query the underlying fact tables. During this lock, the text in V$SQLTEXT displays the transaction command as "ALTER SUMMARY ....." as the syntax for the MV command (assuming it is referring to the ALTER MATERIALIZED VIEW <viewname> COMPILE command.

Has anyone seen this lock scenario happen before? Do I need an additional COMMIT in my auto load process to release the transaction? Any ideas/suggestions would be greatly appreciated.

Thanks
Donna Received on Wed May 07 2003 - 06:38:50 CDT