oracle - Index rebuild from table cursor -

friends... continuing debugging frenzy week... have got curtable part working of user @bob... trying next phase of testing , trying rebuild indexes @ time of owner.table identified rec1.curtable cursor.

i have spent time , again stuck cursor part, please or point mistake?

create or replace procedure movetbl (oldtbs in varchar2, newtbs in varchar2)     authid current user   cursor curtable     select owner, table_name, tablespace_name       dba_tables       tablespace_name = oldtbs       order table_name; rec1 curtable%rowtype;    cursor curindex (tabown in varchar2, tabnam in varchar2)   select table_owner, table_name, owner, index_name, tablespace_name   dba_indexes   table_owner = rec1.owner   , table_name = rec1.table_name; rec2 curindex%rowtype;  begin   rec1 in curtable loop     dbms_output.putline(rec1.owner || '.' || rec1.table_name);      execute immediate 'alter table ' || rec1.owner || '.' || rec1.table_name ||                       ' move tablespace ' || newtbs;     -- starting curindex      open curindex (rec1.owner, rec1.table_name); loop         begin           fetch curindex rec2;         exit when curindex%notfound     dbms_output.put_line ('alter index '||rec2.owner||'.'rec2.index_name||' rebuild tablespace '|| newtbs ||' online')       end;        end loop; -- index cursor loop       close curindex;     end;   end loop;  --curtable loop end movetbl; 

you haven't said issues you're facing, query second cursor isn't right; rec1 isn't in scope. should using cursor parameters:

cursor curindex (tabown in varchar2, tabnam in varchar2)   select table_owner, table_name, owner, index_name, tablespace_name   dba_indexes   table_owner = tabown   , table_name = tabnam; 

the rec1 declaration row type isn't needed cursor synatx you've chosen; rec2 unless change use same loop type. don't need begin/end in inner loop though. , seem have end floating around too. simplify bit to:

begin   rec1 in curtable loop     dbms_output.putline(rec1.owner || '.' || rec1.table_name);      execute immediate 'alter table ' || rec1.owner || '.' || rec1.table_name ||                       ' move tablespace ' || newtbs;      rec2 in curindex (rec1.owner, rec1.table_name) loop        execute immediate 'alter index '||rec2.owner||'.'rec2.index_name         ||' rebuild tablespace '|| newtbs ||' online';      end loop; -- index cursor loop   end loop; -- outer table loop end movetbl; 

you wouldn't need declare rec2 either.