Fast Refresh Of Remote Materialized View With Join Errors Out With ORA-22992 (Doc ID 1266355.1)

Last updated on JULY 30, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Fast refresh of distributed Materialized View (MV) errors out with:

ORA-22992: cannot use LOB locators selected from remote tables.

Complete refresh works, completes without errors.

The definition query of the remote MV refers to a master table that has a LOB type column.

Note that the above error message occurs regardless whether the LOB type column is referenced (e.g. on the SELECT list) in the MV definition query or not.

The MV definition query contains join.

The complete error message  is like the following:

ORA-12008: error in materialized view refresh path
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms