Fix: ORA-12018 : ORA-22992 error : cannot use LOB locators selected from remote tables (with workaround)

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 08 Nov, 2021
  • 0 Comments
  • 54 Secs Read

Fix: ORA-12018 : ORA-22992 error : cannot use LOB locators selected from remote tables (with workaround)

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

1) Sample example:

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>

Issue : Due to XML type column mview not created. “refresh demand” is also not working.

2) From Oracle document:

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

3) My work-around which worked:

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

alter materialized view HR.CHECKLISTMASTER refresh fast;

It is worked for me.