Re: Rebuild implicit index for primary key

Charles Hooper:

> Franz Kruse wrote:
> > I converted a LONG column in a table to CLOB. This caused an
> > implicit index for the primary key to be set to unusable state. Is
> > there a way to rebuild the index without explicitly mentioning its
> > name? Both the conversion LONG to CLOB and the rebuild are in an
> > SQL script where the name of the index is not known. - Or is there
> > a way to avoid the unusable state when converting the LONG column?
> >
> > Any hint is welcome.
> >
> > Franz
>
> Try this:
> SELECT
> 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
> FROM
> DBA_INDEXES
> WHERE
> STATUS='INVALID'
> AND OWNER='owner_name_here'
> AND TABLE_NAME='table_name_here';
>
> The above should produce output with a SQL statement that looks
> something like this:
> ALTER INDEX owner_name_here.SYS_C007058 REBUILD;
>
> Copy the output, then execute the copied output to rebuild the index.

OK, but this would have to be done manually, right? What I am looking for is something I can put in an SQL script and that will work without any manual interaction for several databases that all have the same structure. I should have said that it's an upgrade script used to convert several databases to a new version. - Or is there a possibility to automatically execute the result of a query (the above one) as a new query? That would of course help.

Franz Received on Sat Jan 06 2007 - 09:09:23 CST