IMPORTANT: Remember to check the index for UNUSABLE status after LOB movement

alter table scott.blobs modify lob (doc) (storage (maxextents unlimited));
alter table scott.blobs modify lob (doc) (storage (freelists 5));
alter table scott.blobs modify lob (doc) (allocate extent);
alter table mypartitionedtable modify default attributes lob (mylobcolumn) (tablespace users); --Note:114915.1

select 'alter table ' || table_name || ' modify lob (' || column_name || ') (storage (maxextents unlimited));'
from user_tab_columns where data_type like '_LOB';

select 'alter table ' || table_name || ' modify lob (' || column_name || ') (storage (maxextents unlimited)) (index (storage (maxextents unlimited)));'
from user_tab_columns where data_type like '_LOB';



Note: column_name comes from dba_lobs where index_name = 'SYS_IL...'

--Just move LOB segment
alter table uc.ATTACHEMENTTB move lob (FILEDATA) store as (tablespace lob_ndx);
--Move table and LOB segments
alter table uc.ATTACHEMENTTB move tablespace lob_data lob (FILEDATA) store as SYS_LOB0000022865C00011$$ (tablespace lob_ndx);

alter table tangram.TC_INET_REPORTS move tablespace tangram_data lob (sql) store as SYS_IL0000003185C00002$$ (tablespace tangram_index);

ALTER TABLE foo MOVE
  TABLESPACE new_tbsp STORAGE(new_storage)
  LOB (lobcol) STORE AS lobsegment
  (TABLESPACE new_tbsp STORAGE (new_storage));



  Eg: Create table DemoLob ( A number, B clob )
       LOB(b) 
	STORE AS lobsegname ( 
	  TABLESPACE lobsegts 
	  STORAGE (lobsegment storage clause) 
	  INDEX lobindexname (
		TABLESPACE lobidxts
		STORAGE ( lobindex storage clause ) 
	  ) 
	)
	TABLESPACE tables_ts
	STORAGE( tables storage clause )
     ;