I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:
CREATE TABLE DB1.TEST_TABLE ID NUMBER, FOO CLOB; INSERT INTO DB1.TEST_TABLE (ID, FOO) VALUES (1, "RANDOM TEXT");
Now I created da databaselink to access DB1 from instance 2
SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK
The select works perfect. But if I want to create a view with this select I get an error:
CREATE VIEW DB2.V_TEST_TABLE AS SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK ORA-22992: cannot use LOB locators selected from remote tables 22992. 00000 - "cannot use LOB locators selected from remote tables" *Cause: A remote LOB column cannot be referenced. *Action: Remove references to LOBs in remote tables.
I don't undertand why selecting a distributing lob works fine but creating a view of this select not.