Blog

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.

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.