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>
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
