Question: I
just tried moving all tables and indexes from one tablespace to
another using a command formatted like the following:
alter table a move tablespace b
I then attempted to rebuild all indexes
tablespace b. There is one LOB index in the original table
that did not get moved and attempts to rebuild that index to make it
use the new
tablespace fails with an ORA-02327 error:
ORA-02327: cannot create index on expression with datatype LOB
How do I get this LOB index moved to
tablespace b and resolve this ORA-02327 error?
Answer:
When you use ALTER INDEX MOVE TABLESPACE, you do not need to rebuild the index.
A LOB index cannot be
moved the way you are attempting to move it. The DDL should be
extracted and the index built in another tablespace.
This is confirmed by checking the
oerr utility
to fully display the ORA-02327 error:
ORA-02327: cannot
create index on expression with datatype string
Cause: An attempt
was made to create an index on a non-indexable expression.
Action: Change the
column datatype or do not create the index on an expression whose
datatype is one of VARRAY, nested table, object, LOB, or REF.
The LOB index should be in the
same tablespace as the LOG segment as follows:
SQL> DESC
TESTLOB
Name Null? Type
----------------------------------------- -------- -----------
COL1 NUMBER
COL2 CLOB
SQL> COLUMN SEGMENT_NAME FORMAT A25
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TESTLOB USERS
SYS_IL0000005539C00002$$ USERS
LOB_TESTLOB_COL2 USERS
3 rows selected.
SQL> ALTER TABLE TESTLOB MOVE LOB (COL2) STORE AS (TABLESPACE B);
Table altered.
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TESTLOB USERS
SYS_IL0000005539C00002$$ B
LOB_TESTLOB_COL2 B
3 rows selected.
SQL>
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2011 by Burleson Enterprises
All rights reserved.
Oracle ©
is the registered trademark of Oracle Corporation.
|
|
