Re: Rebuild implicit index for primary key

In message <enoe2o$fvi$03$1_at_news.t-online.com>, Franz Kruse <Franz.Kruse_at_t-online.de> writes
>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?

You can either surround it with
spool rebuild_index.sql

spool off
@rebuild_index.sql

Or you could use an anonymous PL/SQL block

declare
  sqlstmt varchar2(1000);
begin
  SELECT
   'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' into sqlstmt
FROM
   DBA_INDEXES
  WHERE
   STATUS='INVALID'
   AND OWNER='owner_name_here'
   AND TABLE_NAME='table_name_here';

  execute immediate sqlstmt;

end;

(with appropriate error checking of course)

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>