----用途：在线并行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> 
![[Oracle]索引](https://ucc.alicdn.com/pic/developer-ecology/3mfxe2r26qbaq_c50f50e14c2447959ba0c272c4967106.jpeg?x-oss-process=image/resize,h_160,m_lfit)