oracle 索引重建,分区索引重建

普通索引重建会引起报错 ORA-14086 When Rebuilding Index Of Partition Table (Doc ID 2403717.1)

Step 1: Look-up the partition name in dba_ind_partitions view. select Index_owner,Index_name,partition_name from dba_ind_partitions where index_name = ‘<index_name>’;

Step 2: specify the partition name in the index rebuild statement:

1
SQL> alter index <Index_name> rebuild partition <Partition_name>;

普通索引重建 alter index <Index_name> rebuild;

################################################################## GRANT SELECT ANY TABLE TO oracle; GRANT SELECT ANY DICTIONARY TO oracle; GRANT EXECUTE ANY PROCEDURE TO oracle;

1.重建索引 CREATE OR REPLACE PROCEDURE REBUILD_INDEX(USER_NAME IN VARCHAR2) AUTHID CURRENT_USER IS V_SQL1 VARCHAR2(500); V_SQL2 VARCHAR2(500); ACCOUNT NUMBER:= 0; BEGIN FOR LINE1 IN (SELECT T.OWNER, T.INDEX_NAME FROM DBA_INDEXES T WHERE T.OWNER = UPPER(USER_NAME) AND T.STATUS IN (‘UNUSABLE’,‘N/A’)) LOOP V_SQL1 := ‘alter index ’ || LINE1.OWNER || ’.’ || LINE1.INDEX_NAME ||’ rebuild ONLINE PARALLEL 6’; V_SQL2 := ‘alter index ’ || LINE1.OWNER || ’.’ || LINE1.INDEX_NAME ||’ NOPARALLEL’; EXECUTE IMMEDIATE V_SQL1; EXECUTE IMMEDIATE V_SQL2; ACCOUNT := ACCOUNT + 1; END LOOP;

DBMS_OUTPUT.PUT_LINE(‘重建索引数量:‘||ACCOUNT); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END REBUILD_INDEX;

#自动重建 begin — Call the procedure REBUILD_INDEX(user_name => ‘hs_user’); —输入用户名

end;

2.重建分区索引

CREATE OR REPLACE PROCEDURE REBUILD_PARTITION_INDEX(USER_NAME IN VARCHAR2) AUTHID CURRENT_USER IS V_SQL1 VARCHAR2(500); V_SQL2 VARCHAR2(500); ACCOUNT1 NUMBER:= 0; ACCOUNT2 NUMBER:= 0; BEGIN FOR LINE1 IN (SELECT T.INDEX_OWNER, T.INDEX_NAME,T.PARTITION_NAME FROM DBA_IND_PARTITIONS T WHERE T.INDEX_OWNER = UPPER(USER_NAME) AND T.STATUS = ‘UNUSABLE’) LOOP V_SQL1 := ‘alter index ’ || LINE1.INDEX_OWNER || ’.’ || LINE1.INDEX_NAME ||’ rebuild PARTITION ‘||LINE1.PARTITION_NAME||’ ONLINE PARALLEL 6’; EXECUTE IMMEDIATE V_SQL1; ACCOUNT1 := ACCOUNT1 + 1; END LOOP;

FOR LINE2 IN (SELECT DISTINCT T.INDEX_OWNER, T.INDEX_NAME FROM DBA_IND_PARTITIONS T WHERE T.INDEX_OWNER = UPPER(USER_NAME) AND T.STATUS = ‘UNUSABLE’) LOOP V_SQL2 := ‘alter index ’ || LINE2.INDEX_OWNER || ’.’ || LINE2.INDEX_NAME ||’ NOPARALLEL’; EXECUTE IMMEDIATE V_SQL2; ACCOUNT2 := ACCOUNT2 + 1; END LOOP;

DBMS_OUTPUT.PUT_LINE(‘分区数量:‘||ACCOUNT1||‘,重建索引数量:‘||ACCOUNT2);

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END REBUILD_PARTITION_INDEX;

#自动重建

begin — Call the procedure REBUILD_PARTITION_INDEX(user_name => ‘hs_user’); —输入用户名

end;

3.验证 SELECT T.OWNER, T.INDEX_NAME FROM DBA_INDEXES T WHERE T.OWNER = UPPER(”)

SELECT T.INDEX_OWNER, T.INDEX_NAME,T.PARTITION_NAME FROM DBA_IND_PARTITIONS T WHERE T.INDEX_OWNER = UPPER(”) AND T.STATUS = ‘UNUSABLE’