- 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;