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?

[Webinar] Streamline your web hosting managementRegister Today

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.