Re: Rebuild implicit index for primary key

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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 06 2007 - 08:46:43 CST