Check LOB current tablespace
SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1 SYS_IL0000057639C00010$$ LOBINDEX TEMPDATA 65536
USER1 SYS_LOB0000057639C00010$$ LOBSEGMENT TEMPDATA 12713984
USER1 SYS_IL0000057685C00008$$ LOBINDEX TEMPDATA 65536
USER1 SYS_LOB0000057685C00008$$ LOBSEGMENT TEMPDATA 2228224
USER1 SYS_IL0000057693C00008$$ LOBINDEX TEMPDATA 65536
USER1 SYS_LOB0000057693C00008$$ LOBSEGMENT TEMPDATA 131072
6 rows selected.
Generate moving LObs sql
SQL> select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace USER1) ;' from dba_lobs where owner='USER1';
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS(TABLESPACE USER1;'
---------------------------------------------------------------------------------------------
alter table USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
alter table USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
alter table USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
move lobs into new tablespace
SQL>alter table USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
Table altered.
SQL>alter table USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
Table altered.
SQL>alter table USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
Table altered.
check lobs moved into new tablespace
SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1 SYS_IL0000057639C00010$$ LOBINDEX USER1 65536
USER1 SYS_LOB0000057639C00010$$ LOBSEGMENT USER1 12713984
USER1 SYS_IL0000057685C00008$$ LOBINDEX USER1 65536
USER1 SYS_LOB0000057685C00008$$ LOBSEGMENT USER1 2228224
USER1 SYS_IL0000057693C00008$$ LOBINDEX USER1 65536
USER1 SYS_LOB0000057693C00008$$ LOBSEGMENT USER1 131072
6 rows selected.
rebuild UNUSABLE indxes
SQL> select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='USER1' and STATUS='UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------
alter index USER1.idx_customers rebuild;
alter index USER1.idx_prods rebuild;
alter index USER1.idx_orders rebuild;
....
..
.
SQL>alter index USER1.idx_customers rebuild;
Index altered.
SQL>alter index USER1.idx_prods rebuild;
Index altered.
SQL>alter index USER1.idx_orders rebuild;
Index altered.
Please note LOBINDEX will be automatically relocated onto the same new tablespace as LOBSEGMENT, so there is no need to run SQL to relocate LOBINDEX . Otherwise the error will occur.