Fix: ORA-12018 : ORA-22992 error : cannot use LOB locators selected from remote tables (with workaround) — Troubleshoot / Fix for ORA-12018 and ORA-22992 oracle error — When table has XML type column in remote database and you are creating materialized views in local database you may face above error. It is often found in Oracle 11.2.0.2 / 0.3 versions of AIX platform There is problem with creation of materialized view with clob and xmltype columns till 11.2.0.3. This issue got fixed in 11.2.0.3.6 (database patchset update) i.e PSU 6. Please look metalink Doc ID’s (10242202.8 and 1449750.1).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL> CREATE MATERIALIZED VIEW HR.CHECKLISTMASTER
REFRESH fast
AS
SELECT * FROM HR.CHECKLISTMASTER@remotedb_DBLINK

ORA-12018: following error encountered during code generation for “HR”.”CHECKLISTMASTER”
ORA-22992: cannot use LOB locators selected from remote tables
SQL>

create materialized view HR.CHECKLISTMASTER
refresh complete
as
select  *  from HR.CHECKLISTMASTER@remotedb_dblink where rownum>0;

alter materialized view HR.CHECKLISTMASTER refresh fast;