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

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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *