CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
radha
radha

Reputation: 175

How to execute alter command within select statement in a loop in Oracle?

I am trying to Rebuild Indexes of a schema through a script but I am stucked at a point where I get the string ALTER INDEX OWNER.INDEX_NAME REBUILD NOLOGGING through select statement but I am not getting how to execute the alter command ,please guide :

I tried to assign str the value of select query used in 2nd for loop and then execute it but it gave error .

IS
STR VARCHAR2(5000);
BEGIN

    FOR T IN (
     SELECT USERNAME FROM DBA_USERS WHERE USERNAME ='REPORT'
      )
     LOOP
     
     FOR CUR IN 
     (
       SELECT ' ALTER INDEX '||OWNER||'.'||INDEX_NAME|| ' REBUILD NOLOGGING; ' FROM DBA_INDEXES 
         WHERE  OWNER=T.USERNAME AND TEMPORARY='N'
       )
        
        LOOP 
       --- EXECUTE IMMEDIATE STR ;
       INSERT INTO INDEX_REBUILD_HISTORY 
         SELECT DISTINCT  OWNER, TRUNC(LAST_DDL_TIME) from DBA_OBJECTS where OBJECT_TYPE = 'INDEX' 
         AND 
         OWNER=T.USERNAME ;
         COMMIT;
    
         
    END LOOP;
    END LOOP;
    
    END ;

Upvotes: 0

Views: 1265

Answers (2)

EdStevens
EdStevens

Reputation: 3872

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/

Upvotes: 3

Andrew Sayer
Andrew Sayer

Reputation: 2336

"it gave error ." isn't helpful without the actual error you received. That said you've made the same mistake so many others do, you shouldn't include the ";" as part of your dynamic SQL - it's not part of the statement, it's only used by your client to know when to send code to the database.

FOR CUR IN 
     (
       SELECT ' ALTER INDEX '||OWNER||'.'||INDEX_NAME|| ' REBUILD NOLOGGING' ddl_cmd FROM DBA_INDEXES 
         WHERE  OWNER=T.USERNAME AND TEMPORARY='N'
       )
...
EXECUTE IMMEDIATE CUR.ddl_cmd ;

(I've also given the column an alias so you can use it in your loop nicely.

Then

  INSERT INTO INDEX_REBUILD_HISTORY 
         SELECT DISTINCT  OWNER, TRUNC(LAST_DDL_TIME) from DBA_OBJECTS where OBJECT_TYPE = 'INDEX' 
         AND 
         OWNER=T.USERNAME ;

Is not filtering on the index you just rebuilt, it doesn't seem like it's going to get entirely useful information.

That said... Is it really worth rebuilding all your indexes offline and making them unrecoverable? Probably not, if you're doing this more than once and are benefiting then there's probably something that could be changed with your data model to help. Have a good read of this presentation by Richard Foote, a well established Oracle Indexing Expert https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf I doubt you'd come away from it believing that rebuilding all the indexes is a solution.

Upvotes: 3

Related Questions