Re: Move TABLESPACES and problem with VARRAY

On 6 Mar 2004 07:16:00 -0800, jri_at_doneware.de (Jochen) wrote:

>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!!

You are mixing up things. a TYPE is nothing but a pl/sql construct. Objects have NOTHING to do with tablespaces  As such it has nothing to do with tablespaces and it's definition will be stored in the dictionary by default. According to the CREATE TABLE statement above and further, you are looking at the index which is generated automatically on the type, you need to check out the index PK_0. If that index is not in the IDX tablespace you have a case. However, it would look like it doesn't make sense to have a VARRAY for a Primary key, and you should just do away with this awful design. You may want to check out whether you can have primary key constraints on VARRAYs at all.

--
Sybrand Bakker, Senior Oracle DBA