Oracle Rebuild index & partition index (PLSQL)

-- Use For DBA
declare
  cursor q_index is
    select owner, index_name, index_type
    from dba_indexes
    where owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','GSMADMIN_INTERNAL','XDB','WMSYS','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','SYSRAC','AUDSYS','SYSKM','ORACLE_OCM','SYSDG','GSMUSER')
    and partitioned = 'NO'
    and index_type = 'NORMAL';

  cursor q_index_part is
    select index_owner, index_name, partition_name
    from dba_ind_partitions
    where index_owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','GSMADMIN_INTERNAL','XDB','WMSYS','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','SYSRAC','AUDSYS','SYSKM','ORACLE_OCM','SYSDG','GSMUSER');

  cursor q_index_subpart is
    select index_owner, index_name, subpartition_name
    from dba_ind_subpartitions
    where index_owner not in ('SYS','SYSTEM','XS$NULL','OJVMSYS','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','GSMADMIN_INTERNAL','XDB','WMSYS','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','SYSRAC','AUDSYS','SYSKM','ORACLE_OCM','SYSDG','GSMUSER');

  v_count number := 0;
begin
  for rec in q_index
  loop
    begin
      execute immediate 'analyze index ' || rec.owner || '.' || rec.index_name || ' validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.owner || '.' || rec.index_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.owner || '.' || rec.index_name || ' rebuild online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.owner || '.' || rec.index_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.owner || '.' || rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;

  for rec in q_index_part
  loop
    begin
      execute immediate 'analyze index ' || rec.index_owner || '.' || rec.index_name || ' partition (' || rec.partition_name || ') validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_owner || '.' || rec.index_name || ' partition ' || rec.partition_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.index_owner || '.' || rec.index_name || ' rebuild partition ' || rec.partition_name || ' online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_owner || '.' || rec.index_name || ' partition ' || rec.partition_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.index_owner || '.' || rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;

  for rec in q_index_subpart
  loop
    begin
      execute immediate 'analyze index ' || rec.index_owner || '.' || rec.index_name || ' subpartition (' || rec.subpartition_name || ') validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_owner || '.' || rec.index_name || ' subpartition ' || rec.subpartition_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.index_owner || '.' || rec.index_name || ' rebuild subpartition ' || rec.subpartition_name || ' online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_owner || '.' || rec.index_name || ' subpartition ' || rec.subpartition_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.index_owner || '.' || rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;
end;
/

-- Use For User (Schema)
declare
  cursor q_index is
    select index_name, index_type
    from user_indexes
    where partitioned = 'NO'
    and index_type = 'NORMAL';

  cursor q_index_part is
    select index_name, partition_name
    from user_ind_partitions;

  cursor q_index_subpart is
    select index_name, subpartition_name
    from user_ind_partitions;

  v_count number := 0;
begin
  for rec in q_index
  loop
    begin
      execute immediate 'analyze index ' || rec.index_name || ' validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.index_name || ' rebuild online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;

  for rec in q_index_part
  loop
    begin
      execute immediate 'analyze index ' || rec.index_name || ' partition (' || rec.partition_name || ') validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' partition ' || rec.partition_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.index_name || ' rebuild partition ' || rec.partition_name || ' online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' partition ' || rec.partition_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;

  for rec in q_index_subpart
  loop
    begin
      execute immediate 'analyze index ' || rec.index_name || ' subpartition (' || rec.subpartition_name || ') validate structure online';
      begin
        select count(1) into v_count from index_stats where height > 4 or (del_lf_rows/lf_rows) * 100 > 20;
      exception when others then
        v_count := 0;
      end;
      if v_count = 1 then
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' subpartition ' || rec.subpartition_name || ' Rebuilding.');
        execute immediate 'alter index ' || rec.index_name || ' rebuild subpartition ' || rec.subpartition_name || ' online compute statistics';
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || ' ' || rec.index_name || ' subpartition ' || rec.subpartition_name || ' Finish.');
      end if;
    exception 
      when others then
        dbms_output.put_line(rec.index_name || ' Error:' || SQLERRM);
    end;
  end loop;
end;
/

Oracle analyze table or analyze index validate structure failed ORA-00054