how do you execute the alter command in a select statement in oracle?

You use dynamic sql. And you don’t need your outer loop. The filter on that is available in dba_indexes:

create procedure bld_idx
is
vsql varchar2(500);

for x in (select owner,
                 index_name
          from dba_indexes
          where owner = 'REPORT'
          and TEMPORARY='N'
          )
loop
  vsql := ' ALTER INDEX '||x.OWNER||'.'||x.INDEX_NAME|| ' REBUILD NOLOGGING; ';
  dbms_output.put_line(vsql);  --  debugging only
  execute immediate vsql;
end loop;
end;

Note 1: above is off the top of my head. There may be minor syntax issues, but if so you should be able to work them out.

Not 2: Rebuilding indexes is not something that needs to be done in the normal course of things. Richard Foote is probably the foremost authority on the internals of oracle indexes, and he has this to say: https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top