Re: Separating Indices from tables

I have written a Perl/DBI script which creates a SQL script to do just this. It also adjusts the storage params based on current storage charcteristics (may need some hand tunig).  Please contact me directly if you would like a copy.

"Lothar Armbruester" <lothar.armbruester_at_rheingau.netsurf.de> wrote:

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

Mark Zawadzki, late of Waynesboro, Va.
'...there is not a sprig of grass that shoots uninteresting to me.'

                                                 Thomas Jefferson, 1790.