Oracle Database Discussions

Hi Expert,

I am completely stuck in one issue & the issue is moving LOBINDEXES to another tablespace to demystify the isssue, Have a look

select segment_type,segment_name,tablespace_name
from user_segments
where tablespace_name='MITS_TABLES' and segment_type in ('LOBSEGMENT','LOBINDEX'); 2 3

SEGMENT_TYPE SEGMENT_NAME TABLESPACE_NAME
------------------ --------------------------------------------------------------------------------- ------------------------------
LOBINDEX SYS_IL0019115265C00002$$ MITS_TABLES
LOBINDEX SYS_IL0019115300C00003$$ MITS_TABLES
LOBSEGMENT SYS_LOB0019115265C00002$$ MITS_TABLES
LOBSEGMENT SYS_LOB0019115300C00003$$ MITS_TABLES

I want to move lobindex to new tablespace named MITS_INDEXES.

SELECT object_name,object_type FROM user_objects
WHERE object_type = 'LOB'; 2

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0004607151C00010$$ LOB
SYS_LOB0006219372C00007$$ LOB
SYS_LOB0019115265C00002$$ LOB
SYS_LOB0000276381C00045$$ LOB
SYS_LOB0019115300C00003$$ LOB
SYS_LOB0004655508C00047$$ LOB


And while executing dynamic query to move lobs it is giving only for four objects

SELECT 'alter table '||'MITS'||'.'||table_name||' move lob ('||column_name||') store as '||segment_name||' (tablespace MITS_INDEXES);'
from user_lobs; 2

'ALTERTABLE'||'MITS'||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS'||SEGMENT_NAME||'(TABLESPACEMITS_INDEXES);'
------------------------------------------------------------------------------------------------------------------------------------------------------
alter table MITS.JOBS_SCD move lob (REPORT_COLUMN_TX) store as SYS_LOB0004655508C00047$$ (tablespace MITS_INDEXES);
alter table MITS.IDENTITY_CHANGE_EVENT move lob (EVENT_NOTIFICATION_TX) store as SYS_LOB0006219372C00007$$ (tablespace MITS_INDEXES);
alter table MITS.JOBS move lob (REPORT_COLUMN_TX) store as SYS_LOB0000276381C00045$$ (tablespace MITS_INDEXES);
alter table MITS.SRG_REPORT move lob (SQL_TX) store as SYS_LOB0004607151C00010$$ (tablespace MITS_INDEXES);

I am not able to resolve it. Please help on this.

Thanks in advance.