rebuild lob index on a new tablespace

I want to move a blob index created on tablespace a to tablespace b.
I tried to rebuild the index and got this error:
ORA-02327: cannot create index on column with datatype LOB

I also tried to drop the index and got this error:
ORA-22864: cannot ALTER or DROP LOB indexes

Is there any way I can move this index to the new tablespace ??
LVL 5
sbenyoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dassumitConnect With a Mentor Commented:
U can't drop or alter LOB indexes.

U can export the table to HD or DAT and then drop the table. Then u can import the table specifying index tablespace as different one. This should work.
0
 
crsankarCommented:
Try using the following syntax

ALTER TABLE employee MODIFY LOB

This should work.
0
 
sbenyoAuthor Commented:
Hi,
Thanks, but I can't find anywhere I can change the tablespace of the index with this command.
Do you know the exact syntax (if there is...) for that ?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sudhi022299Commented:
I couldn't find any option to move the tablespace used by the lob segment in ALTER TABLE MODIFY LOB statement.
0
 
crsankarCommented:
ALTER TABLE employee MODIFY LOB (resume) (CACHE);

I was referring to this statement. But I think I made a mistake saying that it can be used to change the tablespace. You can change other storage charecteristics though.

Sorry!


0
 
srikant033100Commented:
Hi sebno

U can allocate a different tablespace to the lob at time of creation or at time of adding a lob column


Srikant Sarda

0
 
dassumitCommented:
Thanks.
U can reach me at dassumit@email.com
Thanx and Regards,
Sumit.
DBA
BFL Software India Ltd.
Bangalore, India
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.