Solved

rebuild lob index on a new tablespace

Posted on 2000-05-01
7
6,231 Views
Last Modified: 2008-02-20
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 ??
0
Comment
Question by:sbenyo
7 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 2765551
Try using the following syntax

ALTER TABLE employee MODIFY LOB

This should work.
0
 
LVL 5

Author Comment

by:sbenyo
ID: 2765767
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
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2767961
I couldn't find any option to move the tablespace used by the lob segment in ALTER TABLE MODIFY LOB statement.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:crsankar
ID: 2768206
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
 

Accepted Solution

by:
dassumit earned 100 total points
ID: 2773094
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
 
LVL 4

Expert Comment

by:srikant033100
ID: 2873561
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
 

Expert Comment

by:dassumit
ID: 3098617
Thanks.
U can reach me at dassumit@email.com
Thanx and Regards,
Sumit.
DBA
BFL Software India Ltd.
Bangalore, India
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 41
Deleting Rows from an Oracle Database - Performance 19 73
migration MS SQL database to Oracle 30 71
scheduler notification 9 27
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question