PL/SQL to Rebuild Indexes 1

[InsaneProgrammer]

I'm writing a procedure to rebuild indexes from one tablespace to another. The procedure compiles fine but when I run it I get the following error:

HDEV8> exec rebuild_indexes('PSINDEX','PSINDEX1','N','N');
BEGIN rebuild_indexes('PSINDEX','PSINDEX1','N','N'); END;

*
ERROR at line 1:
ORA-00953: missing or invalid index name
ORA-06512: at "SYS.REBUILD_INDEXES", line 52
ORA-06512: at line 1

The code for the procedure if below:

CREATE OR REPLACE PROCEDURE SYS.REBUILD_INDEXES
(old_tblspc IN VARCHAR2, new_tblspc IN VARCHAR2, drp_old_tblspc IN CHAR, upd_db_stats IN CHAR)

--Procedure rebuilds indexes from one tablespace to another
--Procedure updates TBL_REBUILD_INDEXES with status
--Procedure returns nothing
--Procedure accepts 4 values:
--1)Tablespace where indexes exist
--2)Tablespace where indexes will be rebuilt to
--3)Y/N to drop old_tablespace
--4)Y/N to update database statistics

AS

--DECLARE
  cursor cur_old_indx is
    select owner, index_name
    from dba_indexes
    where tablespace_name = old_tblspc
    order by 1,2;
           
  v_old_indx_cnt    number;
  v_new_indx_cnt    number;
  v_start_time      date;
  v_end_time        date;
  v_not_rebuilt     number;
     
BEGIN
  
 --dbms_output.put_line(old_tblspc);
 --dbms_output.put_line(new_tblspc);
 --dbms_output.put_line(drp_old_tblspc);
 --dbms_output.put_line(upd_db_stats);

  --Get the start time
  select sysdate
  into v_start_time
  from dual;

 --dbms_output.put_line(v_start_time);
    
  --Get the number of indexes in the current tablespace
  select count(*)
  into v_old_indx_cnt
  from dba_indexes
  where tablespace_name = old_tblspc;

 --dbms_output.put_line(v_old_indx_cnt);
  
  --Rebuild the indexes
[COLOR=red]
  for indx in cur_old_indx loop
    execute immediate 'alter index ''' || indx.owner || '.' || indx.index_name || ' rebuild online parallel tablespace ' || new_tblspc || ''''; 
  end loop;
[/color]  
  --Get the number of indexes in the new tablespace
  select count(*) 
  into v_new_indx_cnt
  from dba_indexes
  where tablespace_name = new_tblspc;
  
  --Make sure that all of the indexes got rebuilt
  if v_new_indx_cnt = v_old_indx_cnt then
    --All indexes rebuilt
    v_not_rebuilt := 0;
    
    --Check the drop tablespace parameter
    if upper(drp_old_tblspc) = 'Y' then
      --Drop the old tablespace
      execute immediate 'drop tablespace ''' || old_tblspc || ' including contents and datafiles' || '''';  
    end if;
    
    --Check the update statistics 
    if upper(upd_db_stats) = 'Y' then
      --Update database statistics
      dbms_stats.gather_database_stats();
    end if;
  
  else
    --Number of indexes that failed to rebuild
    v_not_rebuilt := v_new_indx_cnt - v_old_indx_cnt;
  end if;
  
  --Get end time
  select sysdate
  into v_end_time
  from dual;
  
  --Update the log table
  insert into tbl_rebuild_indexes
  values (v_start_time, v_end_time, old_tblspc, new_tblspc, v_not_rebuilt);
  
END;
/

Thanks

InsaneProgrammer.com

 

[carp]

Try the following in place of the code you highlighted:

  for indx in cur_old_indx loop
    execute immediate 'alter index '||indx.owner ||'.'||indx.index_name||' rebuild online parallel tablespace '||new_tblspc; 
  end loop;

 

[InsaneProgrammer]

That seems to have fixed the problem. Thanks for the help.

InsaneProgrammer.com

 

[carp]

You bet.
One thing I've found useful when dealing with EXECUTE IMMEDIATE is, when it doesn't seem to work, try printing out the command to see what it's trying to feed to the engine:

  for indx in cur_old_indx loop
 --   execute immediate 
dbms_output.put_line('alter index ''' || indx.owner || '.' || indx.index_name || ' rebuild online parallel tablespace ' || new_tblspc || ''''); 
  end loop;

In this case, it would have shown you an output something like

alter index 'owner_name.index_name rebuild online parallel tablespace tablespace_name'

which clearly does not conform to SQL syntax.

I'm glad we were able to get you moving again!