Re: MV problem with OR condition on Oracle 8.1.7

Joe -

Have you taken a look at Oracle's Metalink site for this error? Give it a try, assuming that your organization has a support contract, of course.

Anyway, I'll cut to the chase. The list of fixed bugs for Oracle 8.1.7 says that this error should have occurred with 8.1.6, but didn't. Things are behaving the way they should now. Here's the short description describing this fix, directly from Metalink (note 132632.1):

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ Oracle allows an ON COMMIT materialized view to be created when it should signal and ORA-12054 error. This creates a materialized view which then cannot be dropped. (ORA-3113
on drop). This fix prevents the MV being created in the first place, but will not allow a broken MV to be dropped =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

Thanks,
TG

Jw wrote:
>
> I try to create MV with OR condition on Oracle 8.1.7 but with below
> error message. But the same MV was successfully created under Oracle
> 8.1.6. Anybody has any answer for this.
>
> CREATE MATERIALIZED VIEW SAMPLE.MV_TESTING
> PARALLEL BUILD IMMEDIATE
> REFRESH FAST ON COMMIT
> WITH ROWID
> AS
> SELECT ROWID "A_RID", COMPANY_ID, COMPANY_NAME,
> UPPER(COMPANY_NAME) AS COMP_NAME_UPPER
> FROM
> SAMPLE.COMPANY_PROFILE
> WHERE
> COMPANY_TYPE = 'B' OR COMPANY_TYPE = 'A'
>
> ERROR at line 9:
> ORA-12054: cannot set the ON COMMIT refresh attribute for the
> materialized view
>
> Thanks
> Joe
Received on Sun Mar 31 2002 - 22:40:53 CST