Move TABLESPACES and problem with VARRAY

From: Jochen <jri_at_doneware.de>
Date: 6 Mar 2004 07:16:00 -0800
Message-ID: <9331ca31.0403060716.6ccdd72f@posting.google.com>

Hallo,

every help is welcome!

Now i have a nice Oracle type defined as VARRAY like

CREATE OR REPLACE TYPE MY_TYPE AS VARRAY(254) OF NUMBER( 10 ) Then i want to create a Table with to useful tablespaces like

CREATE TABLE MY_TABLE (

	ID NUMBER ( 10 ) NOT NULL,
            PROBLEM_COL MY_TYPE,
	CONSTRAINT PK_0 PRIMARY KEY (ID) USING INDEX TABLESPACE IDX ,


	) 

TABLESPACE OBJ
/

Superfine! All things seems ok, BUT

select * from dba_indexes where owner='USER' AND Not (tablespace_name = 'IDX') shows

OWNER : USER

INDEX_NAME : SYS_IL0000046644C00008$$ 
INDEX_TYPE : LOB
TABLE_OWNER: USER
TABLE_NAME: MY_TABLE

...

I have tried so many statements to create this table and save MY_TYPE in my defined tablespace IDX or OBJ. Only the statement

CREATE TABLE MY_TABLE (

	ID NUMBER ( 10 ) NOT NULL,
            PROBLEM_COL MY_TYPE,
	CONSTRAINT PK_0 PRIMARY KEY (ID) USING INDEX TABLESPACE IDX ,


	) 

ORGANIZATION INDEX TABLESPACE IDX
/

do MY_TYPE to tablespace IDX !!!????

Do know somebody the right option to create or move the tablespace from ALL objects of a table to my defined tablespace? Or know somebody a work-around?

Thanks!! Received on Sat Mar 06 2004 - 09:16:00 CST