Oracle 9i升级19C 迁移关于失效索引的梳理方法

declare   ls_sql      varchar2(1000);   ls_tbs_name  varchar2(30) :='FWDATA_IDX'; begin        for ci in (select owner, index_name, index_type, table_owner, table_name, uniqueness                  from dba_indexes@to9idb                  where owner in ('XXXX')                 --and index_name not like 'SYS_%' --and table_name in (select tab_name from exp_group)                 minus                 select owner, index_name, index_type, table_owner, table_name, uniqueness  from dba_indexes@to19cdcdb                  where owner in ('XXXX')                 --and index_name not like 'SYS_%'                 order by owner, table_name, index_name) loop       if ci.uniqueness = 'UNIQUE' then         ls_sql := 'create unique index ' || ci.owner || '.' || ci.index_name || ' on ' || ci.table_owner || '.' || ci.table_name || '(';       else         ls_sql := 'create index ' || ci.owner || '.' || ci.index_name || ' on ' || ci.table_owner || '.' || ci.table_name || '(';       end if;            for cr in (select * from dba_ind_columns@to9idb where index_owner = ci.owner and index_name = ci.index_name                          and table_owner = ci.table_owner and table_name = ci.table_name order by column_position) loop                  ls_sql := ls_sql || cr.column_name || ',';                end loop;       ls_sql := rtrim(ls_sql, ',');       ls_sql := ls_sql || ') tablespace ' || ls_tbs_name || ' parallel 16;';              dbms_output.put_line(ls_sql);        dbms_output.put_line('alter index ' || ci.owner || '.' || ci.index_name || ' noparallel;');       dbms_output.put_line('');     end loop; end; / -- 添加主键约束 declare   ls_sql      varchar2(1000);   ls_tbs_name  varchar2(30) :='FWDATA_IDX'; begin   for cc in (select owner, table_name                 from dba_constraints@to9idb t                  where owner in ('XXXX')                  and t.constraint_type = 'P' and table_name in (select tab_name from exp_group)                 minus                 select owner, table_name                 from dba_constraints@to19cdcdb t                  where owner in ('XXXX')                  and t.constraint_type = 'P'                 order by owner, table_name) loop     for ci in (select * from dba_constraints@to9idb where owner = cc.owner and table_name = cc.table_name and constraint_type = 'P') loop       ls_sql := 'alter table ' || ci.owner || '.' || ci.table_name || ' add constraint ' || ci.constraint_name || ' primary key (';            for cr in (select * from dba_ind_columns@to9idb where index_owner = ci.owner and index_name = ci.index_name                          and table_owner = ci.owner and table_name = ci.table_name order by column_position) loop                  ls_sql := ls_sql || cr.column_name || ',';                end loop;       ls_sql := rtrim(ls_sql, ',');       ls_sql := ls_sql || ') using index enable;';       dbms_output.put_line(ls_sql);     end loop;   end loop; end; / -- 添加unique键约束 declare   ls_sql      varchar2(1000);   ls_tbs_name  varchar2(30) :='FWDATA_IDX'; begin   for cc in (select owner, table_name                 from dba_constraints@to9idb t                  where owner in ('XXX')                  and t.constraint_type = 'U'                 --and table_name in (select tab_name from exp_group)                 minus                 select owner, table_name                 from dba_constraints@to19cdcdb t                  where owner in ('XXX')                  and t.constraint_type = 'U'                 order by owner, table_name) loop     for ci in (select * from dba_constraints@to9idb where owner = cc.owner and table_name = cc.table_name and constraint_type = 'U') loop       ls_sql := 'alter table ' || ci.owner || '.' || ci.table_name || ' add constraint ' || ci.constraint_name || ' unique (';            for cr in (select * from dba_ind_columns@to9idb where index_owner = ci.owner and index_name = ci.index_name                          and table_owner = ci.owner and table_name = ci.table_name order by column_position) loop                  ls_sql := ls_sql || cr.column_name || ',';                end loop;       ls_sql := rtrim(ls_sql, ',');       ls_sql := ls_sql || ') using index enable;';       dbms_output.put_line(ls_sql);     end loop;   end loop; end; /