Friday, August 16, 2013

Move LOB Segment and Index to a different tablespace

How to move a lob segment and a lob index to a differrent tablespace.
=====================================================================

How to rebuild a LOB Index in oracle
===================================

Normally you cannot move or rebuild a lob index to another tablespace like normal table. so you have to use little difference syntax. this is bellows .... 


Example:-
=================

drop table image ;


CREATE TABLE image (
dname VARCHAR2(30), 
sname VARCHAR2(30), 
fname VARCHAR2(30), 
hblob BLOB); 



ALTER TABLE TEST MOVE TABLESPACE SYSTEM; ----ITS OK 


Above command will move the table to new tablespace but will not move the 
CLOB segment and it will still be in original tablespace. This is because LOB 
data is stored outside of the table.

Check the tablespace of the CLOB column by issuing following sql.


SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';



Alter index SYS_IL0000098512C00004$$ rebuild tablespace SYSTEM; 
---ITS FAIL WITH BELLOWS ERROR 

ORA-02327: cannot create index on expression with datatype LOB


---------ITS OK 
ALTER TABLE image MOVE LOB (hblob)
STORE AS (TABLESPACE system);


----see again 
SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';



SELECT * FROM user_lobs;



[Note : "small" LOBs stored inline (ie in the row itself) are not in a
seperate LOB SEGMENT at all. That is called STORAGE IN ROW and is the
default for LOBs of 4000bytes or less.]



---for generating a script 
select 'alter table '||table_name||' move tablespace YOUR_TS'||chr(10)||
'LOB ('||column_name||') store as '||segment_name||chr(10)||
'(tablespace YOUR_TS);'
from user_lobs

1 comment:

Anonymous said...

Topic was very well explained.

Thank you for the note. Ab.