OraFAQ Forum: Server Administration » Howto migrate a LOB type INDEX to another tablespace (ORA-02327)

Home » RDBMS Server » Server Administration » Howto migrate a LOB type INDEX to another tablespace (ORA-02327)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Howto migrate a LOB type INDEX to another tablespace (ORA-02327) [message #111845]

Mon, 21 March 2005 06:05

donath
Messages: 1
Registered: March 2005

Junior Member

Hoi,

I just moved all tables and indexes (except one) from tablespace ts1 to another tablespace called ts2.

I moved the tables to a different tablespace using "alter table t move tablespace ts2".
I then rebuilded all indexes (except the LOB type one) using the tablespace ts2.

Unfortunately rebuilding the LOB type index to make it use the new tabespace fails with a ORA-02327 error:
"cannot create index on expression with datatype LOB"

I wonder what the best way is to get this LOB index moved to tablespace t2.

Any suggestions?

Thanks,
John

Report message to a moderator

Re: Howto migrate a LOB type INDEX to another tablespace (ORA-02327) [message #111875 is a reply to message #111845]

Mon, 21 March 2005 08:03

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

when you use alter index move tablespace,
you no need to rebuild the index.
Lobindex cannot be moved like that.
extract the ddl and recreate the index in another tablespace.

Report message to a moderator

Re: Howto migrate a LOB type INDEX to another tablespace (ORA-02327) [message #111922 is a reply to message #111875]

Mon, 21 March 2005 20:21

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

It's better for lob index to be in the same tablespace as lob segment.

Report message to a moderator

Re: Howto migrate a LOB type INDEX to another tablespace (ORA-02327) [message #111942 is a reply to message #111922]

Tue, 22 March 2005 00:50

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

Quote:

SQL> DESC LOBTEST
Name Null? Type
----------------------------------------- -------- ----------------------------

COL1 NUMBER
COL2 CLOB

SQL> COLUMN SEGMENT_NAME FORMAT A30
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LOBTEST USERS
SYS_IL0000004428C00002$$ USERS
LOB_LOBTEST_COL2 USERS

3 rows selected.

SQL> ALTER TABLE LOBTEST MOVE LOB (COL2) STORE AS (TABLESPACE TS4G);

Table altered.

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LOBTEST USERS
SYS_IL0000004428C00002$$ TS4G
LOB_LOBTEST_COL2 TS4G

3 rows selected.

SQL>

Report message to a moderator

Previous Topic:	Migrating from Oracle 8 to 10
Next Topic:	the way DBMS_METADATA.GET_DDL can get the object's ddl from sybase database ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 02:31:43 CDT 2024