Re: Separating Indices from tables

Hero R. Post wrote on 23-Feb-98

>Hi there,
 

>I am confronted with a database in which tables and Indices(/constraints)
>have been created in the same tablespace.
>I want to separate the two into at least two tablespaces.
>If I use conventional means, the indices that go with the constraints on the
>tables are created in the same tablespace as the tables.
>Since there are approx. 700 tables I don't want to edit/type in all the
>create scripts.
 

>Is there an easy way to do this from the existing tablespace.

Hello. I would try something like

spool somewhere

select 'alter index '||index_name||' rebuild tablespace ts_index;' from user indexes;

spool off

Well, you eventually have to adjust the syntax slightly and add additional string magic to get the storage clauses right. But I think it's a good starting point.

This solution has the advantage that it uses the old indexes to create the new ones, so the time needed is dramatically shorter in comparision to dropping and recreating the indexes. You will need Oracle 7.3 for this to work, though.

>Hero

Hope that helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |