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.]
+ Connect to the User from the user you want to move
---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
