create or replace procedure p1(arg_table in varchar2) is str varchar2(4000); begin declare cursor c is select index_name from user_indexes where lower(table_name) = lower(arg_table) ; c_row c%rowtype; begin for c_row in c loop SELECT DBMS_METADATA.GET_DDL('INDEX',c_row.index_name) into str from dual; execute immediate 'drop index '||c_row.index_name; execute immediate str; end loop; end; end p1;SQL> select index_name from user_indexes where table_name='T1';INDEX_NAME ------------------------------ T2_IDX2 T1_IDX1SQL> exec p1('T1'); DROP CREATE DROP CREATEPL/SQL 过程已成功完成。SQL> select index_name from user_indexes where table_name='T1';INDEX_NAME ------------------------------ T2_IDX2 T1_IDX1 疑问:同名同列,直接rebuild就可以了,为啥要删了重建呢
str varchar2(4000);
begin
declare
cursor c is select index_name from user_indexes where lower(table_name) = lower(arg_table) ;
c_row c%rowtype;
begin
for c_row in c loop
SELECT DBMS_METADATA.GET_DDL('INDEX',c_row.index_name) into str from dual;
execute immediate 'drop index '||c_row.index_name;
execute immediate str;
end loop;
end; end p1;SQL> select index_name from user_indexes where table_name='T1';INDEX_NAME
------------------------------
T2_IDX2
T1_IDX1SQL> exec p1('T1');
DROP
CREATE
DROP
CREATEPL/SQL 过程已成功完成。SQL> select index_name from user_indexes where table_name='T1';INDEX_NAME
------------------------------
T2_IDX2
T1_IDX1
疑问:同名同列,直接rebuild就可以了,为啥要删了重建呢