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