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;

/