Home » RDBMS Server » Server Administration » Cannot Rebuild Index.
Cannot Rebuild Index. [message #64103] Wed, 15 December 2004 22:16 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Dear frends,

I have a index which can not rebuild it's giving following error..

SQL> alter index SYS_IL0000033336C00005$$ rebuild;
alter index SYS_IL0000033336C00005$$ rebuild
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

I have one column in this table which has a BLOB datatype..

is this a thing that i can not rebuild index for LOB type..I want to rebuild it because del_lf_rows/lf_rows ratio exceeds 50% for this index.

Pl. Help me..Regarding This..

Bhavin shah
Re: Cannot Rebuild Index. [message #64105 is a reply to message #64103] Thu, 16 December 2004 00:06 Go to previous messageGo to next message
Dilip
Messages: 12
Registered: January 2002
Junior Member
Hi

i am sure that you can't drop/alter a index on LOB's column...but there is alternate option check out it suits for u or not create a another table on your existing table....(create table name as select...statement)this will create segment with a new extents...drop the older table and rename the new table...

Regards
D.Dilip
Re: Cannot Rebuild Index. [message #64107 is a reply to message #64103] Thu, 16 December 2004 02:27 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I want to rebuild it because del_lf_rows/lf_rows ratio exceeds 50% for this index.

What are you going to acheive with this?
Nothing.		
The index will go the same state as it was before easily.
Rebuilding the index is Not going to help with anything at all.

If you want to release the extents, Move the lob table ( along with lob index) to another tablespace and bring it back;
or
just move it to same tablespace ( if you have enough space).

mag@mutation_mutation > create table test (c1 blob);

Table created.

mag@mutation_mutation > select tablespace_name,index_name from user_indexes
  2   where table_name='TEST';

TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------
USERS                          SYS_IL0000013450C00001$$

mag@mutation_mutation > alter table test move tablespace users lob(c1) store as lobsegment (tablespace users);

Table altered.

Previous Topic: Out of memory - Windows
Next Topic: Increasing client licences
Goto Forum:
  


Current Time: Thu Jan 09 22:52:30 CST 2025