时间戳

首页 > 解决方案 > 删除分区和更新全局索引时存在错误：分区维护操作可能无法与其他操作组合

oracle - 删除分区和更新全局索引时存在错误：分区维护操作可能无法与其他操作组合

问题描述

这里我写了一个Oracle SP来删除分区并更新全局索引。但它遇到了一个错误，

begin
EXECUTE IMMEDIATE 'alter session set nls_date_format="YYYY-MM-DD"';
dbms_output.put_line(need_housekeeping_month);

EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (need_housekeeping_month))  UPDATE GLOBAL INDEXES';

end;

我搜索了一些解决方案，但不知道，请帮助。

 ORA-14048: a partition maintenance operation may not be combined with other operations

        Cause: The ALTER TABLE or ALTER INDEX statement attempted to combine a partition maintenance operation (for example, MOVE PARTITION) with some other operation (for example, ADD PARTITION or PCTFREE) which is illegal.

        Action: Ensure that a partition maintenance operation is the sole operation specified in an ALTER TABLE or ALTER INDEX statement; operations other than those dealing with partitions, default attributes of partitioned tables/indices, or specifying that a table be renamed (ALTER TABLE RENAME) can be combined.

添加另一个问题

    create or replace procedure   SP_HOUSE_KEEPING(p_dt_cycle_dt in VARCHAR2) is
hotspot_data_months number :=27;
need_housekeeping_month DATE := ADD_MONTHS( to_date(p_dt_cycle_dt,'yyyymmdd'), -(hotspot_data_months-1) );
begin
EXECUTE IMMEDIATE 'alter session set nls_date_format="YYYY-MM-DD"';
dbms_output.put_line(need_housekeeping_month);
EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (need_housekeeping_month)  UPDATE GLOBAL INDEXES';
end;

ORA-14755: FOR VALUES 子句的分区规范无效。

标签： oraclepartitioning

解决方案

你不能把分区作为变量，试试这个：

EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (DATE '''||to_char(need_housekeeping_month, 'YYYY-MM-DD')||''') UPDATE GLOBAL INDEXES';

推荐阅读