同时设置DROP和CREATE索引 [英] DROP and CREATE index at the same time

问题描述

我们的表所有者名称和索引所有者名称不匹配.我们需要删除索引以及与之关联的约束，并在特定模式中重新创建该索引以匹配索引所有者和表所有者的名称.对于每个索引，我们有不同的列数，范围从1到4. 我需要创建一个用于同时删除n重新创建的过程.

We have table owner name and index owner name mismatch. We need to drop the index as well the constraints associated with it and recreate the same in the particular schema to match index owner and table owner name. We have different column count for each index varying from 1 to 4. I need to create a procedure for dropping n recreating at the same time.

declare
total number(10);
table_name varchar2(255);
index_name varchar2(255);
column_name varchar2(255);
begin
select  a.table_name, a.index_name,count(a.column_name) into table_name, index_name,total
from all_ind_columns a, all_indexes b where a.index_name = b.index_name
and b.table_owner = 'SIM'and b.owner like 'TIM'
group by a.table_name,a.index_name
order by a.table_name,a.index_name,count(a.column_name);
select c.column_name into column_name from all_ind_columns c,all_indexes d 
where c.index_name=d.index_name 
and d.table_owner='SIM' and d.owner like 'TIM';
for i in (select  a.table_name, a.index_name,count(a.column_name) from all_ind_columns a, all_indexes b where a.index_name = b.index_name
and b.table_owner = 'SIM'and b.owner like 'TIM'
group by a.table_name,a.index_name
order by a.table_name,a.index_name,count(a.column_name))
loop
If i.count(a.column_name)=1 then
DBMS_OUTPUT.PUT_LINE ('Create index'||index_name|| 'on' ||table_name||'('||Column_name||')');
else total>=2
then
DBMS_OUTPUT.PUT_LINE ('Create index'||index_name|| 'on' ||table_name||'('||Column_name,Column_name||')');
end if;
end loop;
end;

推荐答案

DBMS_METADATA 是重新生成对象DDL的官方方法.通常比手动创建DDL语句要好.有数百种创建索引的选项，DBMS_METADATA.GET_DDL是确保您准确地重新创建对象的唯一方法.

DBMS_METADATA is the official way to regenerate object DDL. It's usually better than manually creating DDL statements. There are hundreds of options for creating indexes, DBMS_METADATA.GET_DDL is the only way to ensure you accurately recreate the object.

declare
    v_index_ddl clob;
begin
    for indexes_in_wrong_schema in
    (
        select table_name, index_name
        from all_indexes
        where table_owner = 'SIM' and owner like 'TIM'
    ) loop
        v_index_ddl := dbms_metadata.get_ddl('INDEX', 
            indexes_in_wrong_schema.index_name, 'TIM');
        --Or replace these with "execute immediate" to run instead of print.
        dbms_output.put_line('drop index TIM.'||
            indexes_in_wrong_schema.index_name);
        dbms_output.put_line(replace(v_index_ddl, '"TIM"', '"SIM"'));
    end loop;
end;
/

特权

DBMS_METADATA要求您拥有SELECT_CATALOG_ROLE或以架构所有者身份执行.询问DBA或其他特权用户是否可以授予您该角色.或将上述PL/SQL块转换为函数，在所有者的架构上安装该函数，然后按grant execute on new_function to your_user;.

DBMS_METADATA requires you either have the SELECT_CATALOG_ROLE or are executing as the schema owner. Ask a DBA or other privileged user if they can grant you that role. Or convert the above PL/SQL block into a function, install the function on the owner's schema, and then grant execute on new_function to your_user;.

这篇关于同时设置DROP和CREATE索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！