----用途：在线并行rebuild失效索引

参数输入(不区分大小写)：1.用户,2,表名(可选),3，索引名(可选)

   1.如果只输用户，rebuild此用户下所有失效索引
   2.如果输入用户+表名，rebuild用户下此表失效索引
   3.如果输入用户+表名+index，rebuild用户下此表上指定的失效索引

----使用方法eg:

使用sys用户建好p_rebuild_unusable_index,建同义词,授权给某个需要使用的用户
CREATE PUBLIC SYNONYM p_rebuild_unusable_index FOR SYS.p_rebuild_unusable_index;
GRANT EXECUTE ON SYS.p_rebuild_unusable_index TO 用户;
begin 
  p_rebuild_unusable_index('userA','userA0530');
end;
/
或    
exec p_rebuild_unusable_index('userA');  ---把userA用户下所有失效索引重建
exec p_rebuild_unusable_index('userA','userA0530'); ---把userA用户下userA0530表上的所有失效索引重建
exec p_rebuild_unusable_index('userA','userA0530','idx_userA0530_normal01'); --把把userA用户下userA0530表上的失效索引idx_userA0530_normal01重建

1. p_rebuild_unusable_index 创建脚本

--用途：在线并行rebuild失效索引
--输入,不区分大小写：1.用户,2,表名(可选),3，索引名(可选)
--1.如果只输用户，rebuild此用户下所有失效索引
--2.如果输入用户+表名，rebuild用户下此表失效索引
--eg: exec p_rebuild_unusable_index('userA','table1','index_name');



CREATE OR REPLACE PROCEDURE SYS.p_rebuild_unusable_index(p_owner   IN VARCHAR2 DEFAULT  NULL,
                                              p_table   IN VARCHAR2 DEFAULT    NULL,
                                              p_index   IN VARCHAR2 DEFAULT   NULL) AS
BEGIN

dbms_output.put_line(CHR(10)||'输入参数值为：Owner: '||p_owner||CHR(10)||'Table:'||p_table||CHR(10)||'Index:'||p_index||CHR(10) );  

---传table,owner,不传index
  IF     p_owner is not null
     and p_table is not null
     and p_index is  null
     
  THEN  
--1.重建因drop分区而不可用的普通索引    
  dbms_output.put_line(CHR(10)||'1.重建因drop分区而不可用的普通索引' );  
  --循环游标
  FOR y IN (SELECT owner,
       index_name,
       table_name,
       table_owner,
       'alter index ' || owner || '.' || index_name ||
       ' rebuild online parallel 4 tablespace ' || tablespace_name  as rebuild_index_pl ,
       'alter index ' || owner || '.' || index_name ||' noparallel'  as alert_index_no_pl
  FROM dba_indexes where owner=upper(p_owner) and table_name=upper(p_table) AND partitioned='NO' and  status = 'UNUSABLE'
)
 LOOP
     IF y.index_name IS not NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.rebuild_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.alert_index_no_pl||';');
        EXECUTE IMMEDIATE y.rebuild_index_pl;
        EXECUTE IMMEDIATE y.alert_index_no_pl;
    END IF;
  
   dbms_output.put_line('--- Rebuild  index y.index_name Finished !');  

    END LOOP;

--2.重建因drop分区而不可用的分区索引 
  dbms_output.put_line(CHR(10)||'2.重建因drop分区而不可用的分区索引' );     
FOR g IN (SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild partition ' || s.partition_name || ' online parallel 4 TABLESPACE ' ||s.tablespace_name  as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_partitions s,dba_indexes d where s.index_name=d.index_name and owner=upper(p_owner) and  d.table_name = upper(p_table) and s.status = 'UNUSABLE' 
)
 LOOP
      IF g.index_name IS NOT NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.rebuild_p_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.alert_p_index_no_pl||';');
       EXECUTE IMMEDIATE g.rebuild_p_index_pl;
       EXECUTE IMMEDIATE g.alert_p_index_no_pl;
      END IF;
  dbms_output.put_line('--- Rebuild index partitions Finished !');  

    END LOOP;  
    
--3.重建因drop分区而不可用的子分区索引   
   dbms_output.put_line(CHR(10)||'3.重建因drop分区而不可用的子分区索引 ' );   
FOR p IN (
SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild subpartition ' || s.subpartition_name || '  online parallel 4 TABLESPACE  ' ||s.tablespace_name as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_subpartitions s,dba_indexes d
 WHERE  s.index_name =d.index_name and  owner=upper(p_owner) and d.table_name = upper(p_table) and s.status = 'UNUSABLE' 
  
)
 LOOP
  IF p.index_name IS not NULL
      THEN
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.rebuild_p_index_pl||';');
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.alert_p_index_no_pl||';');
        EXECUTE IMMEDIATE p.rebuild_p_index_pl;
        EXECUTE IMMEDIATE   p.alert_p_index_no_pl;
      END IF;    
        dbms_output.put_line('--- Rebuild index subpartitions Finished !');  

    END LOOP;    
    
---传table,owner,index
ELSIF     p_owner is not null
      and p_table is not null
      and p_index is not null
     THEN    
      
--1.重建因drop分区而不可用的全局索引    
  dbms_output.put_line(CHR(10)||'1.重建因drop分区而不可用的全局索引' );  
  FOR y IN (SELECT owner,
       index_name,
       table_name,
       table_owner,
       'alter index ' || owner || '.' || index_name ||
       ' rebuild online parallel 4 tablespace ' || tablespace_name  as rebuild_index_pl ,
       'alter index ' || owner || '.' || index_name ||' noparallel'  as alert_index_no_pl
  FROM dba_indexes where owner=upper(p_owner) and table_name=upper(p_table)  and index_name = upper(p_index) AND partitioned='NO' and  status = 'UNUSABLE'
)
 LOOP
     IF y.index_name IS NOT NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.rebuild_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.alert_index_no_pl||';');
        EXECUTE IMMEDIATE y.rebuild_index_pl;
        EXECUTE IMMEDIATE y.alert_index_no_pl;
      END IF;
  
   dbms_output.put_line('--- Rebuild nomarl index  Finished !');  

    END LOOP;

--2.重建因drop分区而不可用的分区索引 
  dbms_output.put_line(CHR(10)||'2.重建因drop分区而不可用的分区索引' );     
FOR g IN (SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild partition ' || s.partition_name || ' online parallel 4 TABLESPACE ' ||s.tablespace_name  as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_partitions s,dba_indexes d where s.index_name=d.index_name and d.owner=upper(p_owner) and  d.table_name = upper(p_table) and d.index_name = upper(p_index)  and s.status = 'UNUSABLE' 
)
 LOOP
      IF g.index_name IS NOT NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.rebuild_p_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.alert_p_index_no_pl||';');
       EXECUTE IMMEDIATE g.rebuild_p_index_pl;
       EXECUTE IMMEDIATE g.alert_p_index_no_pl;
      END IF;
  dbms_output.put_line('--- Rebuild index partitions Finished !');  

    END LOOP;  
    
--3.重建因drop分区而不可用的子分区索引   
   dbms_output.put_line(CHR(10)||'3.重建因drop分区而不可用的子分区索引 ' );   
FOR p IN (
SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild subpartition ' || s.subpartition_name || '  online parallel 4 TABLESPACE  ' ||s.tablespace_name as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_subpartitions s,dba_indexes d
 WHERE  s.index_name =d.index_name and  d.owner=upper(p_owner) and d.table_name = upper(p_table) and d.index_name = upper(p_index) and s.status = 'UNUSABLE' 
  
)
 LOOP
  IF p.index_name IS NOT NULL
      THEN
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.rebuild_p_index_pl||';');
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.alert_p_index_no_pl||';');
        EXECUTE IMMEDIATE p.rebuild_p_index_pl;
        EXECUTE IMMEDIATE   p.alert_p_index_no_pl;
      END IF;    
        dbms_output.put_line('--- Rebuild index subpartitions Finished !');  

    END LOOP; 
    
---传owner,不传table,index
ELSIF       p_owner is not null
        and p_table is  null
        and p_index is  null
     THEN    

--1.重建因drop分区而不可用的全局索引    
  dbms_output.put_line(CHR(10)||'1.重建因drop分区而不可用的全局索引' );  
  FOR y IN (SELECT owner,
       index_name,
       table_name,
       table_owner,
       'alter index ' || owner || '.' || index_name ||
       ' rebuild online parallel 4 tablespace ' || tablespace_name  as rebuild_index_pl ,
       'alter index ' || owner || '.' || index_name ||' noparallel'  as alert_index_no_pl
  FROM dba_indexes where owner=upper(p_owner)  AND partitioned='NO' and  status = 'UNUSABLE'
)
 LOOP
     IF y.index_name IS NOT NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.rebuild_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||y.alert_index_no_pl||';');
        EXECUTE IMMEDIATE y.rebuild_index_pl;
        EXECUTE IMMEDIATE y.alert_index_no_pl;
      END IF;
  
   dbms_output.put_line('--- Rebuild nomarl index  Finished !');  

    END LOOP;

--2.重建因drop分区而不可用的分区索引 
  dbms_output.put_line(CHR(10)||'2.重建因drop分区而不可用的分区索引' );     
FOR g IN (SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild partition ' || s.partition_name || ' online parallel 4 TABLESPACE ' ||s.tablespace_name  as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_partitions s,dba_indexes d where s.index_name=d.index_name and d.owner=upper(p_owner)  and s.status = 'UNUSABLE' 
)
 LOOP
      IF g.index_name IS NOT NULL
      THEN
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.rebuild_p_index_pl||';');
       dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||g.alert_p_index_no_pl||';');
       EXECUTE IMMEDIATE g.rebuild_p_index_pl;
       EXECUTE IMMEDIATE g.alert_p_index_no_pl;
      END IF;
  dbms_output.put_line('--- Rebuild index partitions Finished !');  

    END LOOP;  
    
--3.重建因drop分区而不可用的子分区索引   
   dbms_output.put_line(CHR(10)||'3.重建因drop分区而不可用的子分区索引 ' );   
FOR p IN (
SELECT s.index_owner,
       s.index_name,
       'alter index ' || s.index_owner || '.' || s.index_name ||' rebuild subpartition ' || s.subpartition_name || '  online parallel 4 TABLESPACE  ' ||s.tablespace_name as rebuild_p_index_pl,
       'alter index ' || s.index_owner || '.' || s.index_name ||' noparallel'  as alert_p_index_no_pl
  FROM dba_ind_subpartitions s,dba_indexes d
 WHERE  s.index_name =d.index_name and  d.owner=upper(p_owner)  and s.status = 'UNUSABLE' 
  
)
 LOOP
  IF p.index_name IS NOT NULL
      THEN
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.rebuild_p_index_pl||';');
        dbms_output.put_line(CHR(32)||CHR(32)||CHR(32)||p.alert_p_index_no_pl||';');
        EXECUTE IMMEDIATE p.rebuild_p_index_pl;
        EXECUTE IMMEDIATE   p.alert_p_index_no_pl;
      END IF;    
        dbms_output.put_line('--- Rebuild index subpartitions Finished !');  

    END LOOP;


 ELSE
    raise_application_error(-30000,
                            'Wrong number or types of arguments in call to ''p_rebuild_unusable_index''.');
  END IF;    
  
  exception
    when others then
      --异常发生时执行（如输出异常信息、插入日志，数据库回滚等）
      dbms_output.put_line(sqlcode);  
      dbms_output.put_line(sqlerrm);  --sqlcode和sqlerrm是oracle内置函数，用于返回错误代码及描述
END;

2. 建同义词,授权给某个需要使用的用户

CREATE PUBLIC SYNONYM p_rebuild_unusable_index
FOR SYS.p_rebuild_unusable_index;

GRANT EXECUTE ON SYS.p_rebuild_unusable_index TO user;

3. 使用方法

begin 
  p_rebuild_unusable_index('userA','userA0530');
end;
/
或    
exec p_rebuild_unusable_index('userA');  ---把userA用户下所有失效索引重建
exec p_rebuild_unusable_index('userA','userA0530'); ---把userA用户下userA0530表上的所有失效索引重建
exec p_rebuild_unusable_index('userA','userA0530','idx_userA0530_normal01'); --把把userA用户下userA0530表上的失效索引idx_userA0530_normal01重建

4. 使用例子：

建测试表

--
----userA0530  (Table) 
--
CREATE TABLE userA0530
(
  ID          NUMBER                            NOT NULL,
  NAME        VARCHAR2(240 BYTE),
  UPDATETIME  DATE                              NOT NULL,
  address  varchar2(200),
  job     varchar2 (200) 
)
NOCOMPRESS 
PARTITION BY RANGE (UPDATETIME)
INTERVAL( NUMTODSINTERVAL(1,'DAY'))
(  
  PARTITION VALUES LESS THAN (TO_DATE(' 2021-12-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS ,  
  PARTITION VALUES LESS THAN (TO_DATE(' 2022-01-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
)
NOCACHE
MONITORING;

----本地分区索引
--
-- IDX_EQPTSTATUSLOG_21  (Index) 
--
CREATE INDEX idx_userA0530_local ON userA0530
(ID)
  LOCAL;
----普通索引
---
-- IDX_userA0530_N11  (Index) 
--
CREATE INDEX idx_userA0530_normal01 ON userA0530
(NAME)
LOGGING;

----普通索引
--
-- IDX_userA0530_N3  (Index) 
--
CREATE INDEX idx_userA0530_normal02 ON userA0530
(ID, NAME)
LOGGING;

----全局分区索引
create index idx_userA0530_global on userA0530(name,id) 
global partition by hash(name)
( partition p1,
  partition p2,
  partition p3
);

Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (1, 'a', TO_DATE('12/15/2021 09:24:49', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (2, 'b', TO_DATE('12/16/2021 09:24:49', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (3, 'c', TO_DATE('12/17/2021 09:24:49', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (4, 'd', TO_DATE('12/18/2021 09:24:49', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (9, 'j', TO_DATE('01/08/2022 10:24:43', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (9, 'j', TO_DATE('01/08/2022 10:41:45', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (9, 'j', TO_DATE('01/08/2022 11:17:45', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
Insert into userA0530
   (ID, NAME, UPDATETIME,address,job)
 Values
   (9, 'j', TO_DATE('01/08/2022 11:19:46', 'MM/DD/YYYY HH24:MI:SS'),'aaa','aaa');
COMMIT;

####
insert into userA0530 (ID, NAME, UPDATETIME) values (1,'a',sysdate-30);
insert into userA0530 (ID, NAME, UPDATETIME)values (2,'b',sysdate-29);
insert into userA0530 (ID, NAME, UPDATETIME)values (3,'c',sysdate-28);
insert into userA0530 (ID, NAME, UPDATETIME)values (4,'d',sysdate-27);
insert into userA0530 (ID, NAME, UPDATETIME)values (5,'e',sysdate-10);
insert into userA0530 (ID, NAME, UPDATETIME)values (6,'f',sysdate-9);
insert into userA0530 (ID, NAME, UPDATETIME)values (7,'g',sysdate-8);
insert into userA0530 (ID, NAME, UPDATETIME)values (8,'h',sysdate-7);
insert into userA0530 (ID, NAME, UPDATETIME)values (9,'j',sysdate-6);
commit;

SQL> begin 
    p_rebuild_unusable_index('userA','userA20220114');
    end;  /  
1.重建因drop分区而不可用的全局索引
alter index userA.IDX_userA20220114_N11 rebuild online parallel 4 tablespace TEST1;
alter index userA.IDX_userA20220114_N11 noparallel;
--- Rebuild nomarl index  Finished !
alter index userA.IDX_userA20220114_N3 rebuild online parallel 4 tablespace TEST1;
alter index userA.IDX_userA20220114_N3 noparallel;
--- Rebuild nomarl index  Finished !

2.重建因drop分区而不可用的分区索引

3.重建因drop分区而不可用的子分区索引

PL/SQL procedure successfully completed.

**或

SQL> exec p_rebuild_unusable_index('userA','userA20220114');

1.重建因drop分区而不可用的全局索引

2.重建因drop分区而不可用的分区索引

3.重建因drop分区而不可用的子分区索引

PL/SQL procedure successfully completed.

SQL>