Wednesday, January 12, 2011

How to rebuild a LOB Index in oracle

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

5 comments:

Anonymous said...

I just wanted to say thanks for posting this information. It was a great help to me.

Anonymous said...

Thanks! I was looking for this only. It helped me a lot.

Anonymous said...

Thanks for the code examples, had to tweek the code generator example a little but it put me on the right path.

Bharat said...

Great Post!!

It's help me lots of.
Thanks & Appreciate your assistant.
Keep going on....

Anonymous said...

It's great.. but if the table is partitioned table you can't use syntax

alter table...move lob() store as ()

This raises following error

ORA-14511: cannot perform operation on a partitioned object