我有一个要求,我们需要删除未删除的历史分区。要求是一步删除多个分区,而不是一个接一个地删除。
喜欢
ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,
sales_q3_2008, sales_q4_2008 update global indexes;
代替
ALTER TABLE sales DROP PARTITION sales_q1_2008 update global indexes;
ALTER TABLE sales DROP PARTITION sales_q2_2008 update global indexes;
ALTER TABLE sales DROP PARTITION sales_q3_2008 update global indexes;
等等...
应用团队有这段代码:-
CREATE OR REPLACE PROCEDURE Test_user.DROP_PARTITIONS (p_table_name in varchar2, p_partition_key in number)
is
pragma autonomous_transaction;
begin
for cur in (
select partition_name from user_tab_partitions
where table_name = p_table_name
and substr(partition_name,1,8) <= 'P_'||trim(to_char(p_partition_key,'000000'))
order by partition_position desc
) loop
-- wait for processes which might have a lock on the partition to drop (max 10min)
execute immediate 'lock table '||p_table_name||' partition ('||cur.partition_name||') in exclusive mode wait 600';
execute immediate 'alter table '||p_table_name||' drop partition '||cur.partition_name || ' UPDATE GLOBAL INDEXES';
end loop;
end;
/
但我想这是对每个分区进行串行删除,从而创建许多 alter 语句,而不是删除所有必要分区的 1 步命令。
我们如何适应这种情况?