ORA-02327: cannot create index on expression with datatype LOB | Move Lob Objects to New Tablespace using dba_lobs and dba_lob_partitions views

Mehmet Salih Deveci

4 years ago

Hi,

I got ” ORA-02327: cannot create index on expression with datatype LOB ” error during table or partition moving.

You can read the following post with this post to learn more details about moving of Lob objects to new tablespaces.

ORA-14257: cannot move a partition which is a composite partition | Move Table, Partition, Subpartitions to New Tablespace

Details of error are as follows.

alter index MSDBA.SYS_IL0000102354C00044$$ rebuild partition SYS_IL_P3811180 tablespace NEW_TABLESPACE_NAME
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB


alter index MSDBA.SYS_IL0000102453C00044$$ rebuild partition SYS_IL_P3811183 tablespace NEW_TABLESPACE_NAME
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

This error is related with the Lob Objects.

To solve this error, you can move this lob partitions as follows.

ALTER TABLE MSDBA.TEST_INDEX partition SYS_P108695 lob(JOB_DYNAMIC_TASK) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

You can generate the lob partitions moving with the following script.

select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE NEW_TABLESPACE_NAME) ;' from  dba_lob_partitions where  TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

Above script will generate the bulk move scripts, copy them and paste new editor and run them to move objects.

You can generate moving of lob objects with the following script.

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) ;' from dba_lobs where tablespace_name='OLD_TABLESPACE_NAME';

If you want to move other objects ( Table, index, Partitions,subpartitions and etc ) out of Lob objects, you can read the following posts.

How to Rebuild Unused Indexes or Move Another Tablespace in Oracle

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/