create or replace procedure CC_REBUILD_IDX(O_User in Varchar2,
IDX_TABLESPACE in Varchar2) is
IndexName varchar2(255);
v_ErrorText varchar2(1000);
Ret number(2);
cursor get_Index is
SELECT index_name FROM all_indexes WHERE owner = O_User;
begin
-- 重建用户下的所有索引到一表空间
open get_Index;
loop
fetch get_Index
into IndexName;
begin
execute immediate 'alter index ' || IndexName ||
' rebuild tablespace ' || IDX_TABLESPACE;
commit;
exception
when others then
v_ErrorText := IndexName || lpad(to_char(SQLCODE), 5, '0') ||
' 错误原因为:' || SUBSTR(SQLERRM, 1, 200);
INSERT INTO T_errorlog (errorlogid, errocontent) VALUES (CMS_ERRINFO_SEQ.Nextval, v_ErrorText);
commit;
end;
exit when get_Index%notfound; -- 无此会死循环
end loop;
close get_Index;
end CC_REBUILD_IDX;